creating colums having multiple values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

creating colums having multiple values

Author

Topic

Vipul03
Starting Member

5 Posts

Posted - 2009-04-29 : 03:36:05

I am new to Sql and want to create a column say phone no. with multiple values

can i do something like
create table employees (name varchar(25), id int primary key, designation varchar(25), phoneno int multiple)

For any help thanks in advance

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-29 : 04:12:01

What do you mean by multiple values?

You would nearly always set a phone number to varchar or nvarchar, simply because most numbers start with 0, which an int will get rid of. Also, international numbers can either begin with 00 or +

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-29 : 05:35:25

There is not column with multiple values - thank god!
Create a second table phone with relation to table employees via employees.id

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Vipul03
Starting Member

5 Posts

Posted - 2009-04-29 : 07:14:11

quote:
Originally posted by webfred

There is not column with multiple values - thank god!
Create a second table phone with relation to table employees via employees.id

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

but having that table is not a proper solution because that will cause repetition of employee id
furthermore all numbers are unique and u can not assign a phone no. to two user, so if u would be able to create a column with multiple values it will be of great significance

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-29 : 08:00:48

Sorry - I do not agree.
One employee can have more than one phonenumber. that is a classic one to many relationship.
In a phone table you are also able to store more information to a phonenummer.
For Example phone table should have:
phonId,
employeesId,
rank, -- which number is best to use for a call
numbertype, -- 1=normal, 2=mobil, 3=fax etc
number

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Vipul03
Starting Member

5 Posts

Posted - 2009-04-29 : 08:18:18

quote:
Originally posted by webfred

Sorry - I do not agree.
One employee can have more than one phonenumber. that is a classic one to many relationship.
In a phone table you are also able to store more information to a phonenummer.
For Example phone table should have:
phonId,
employeesId,
rank, -- which number is best to use for a call
numbertype, -- 1=normal, 2=mobil, 3=fax etc
number

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

i understand what u r saying but for instance i have to repeat employee id multiple times
Also phoneno is unique so no need of phoneid also i m only considering only mobile for employees

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-29 : 08:25:38

So what if you repeat EmpID. Its not repeated in the Employee table. It is a FOREIGN KEY. That is its EXPRESS PURPOSE. Fred is trying to show you the correct way, not the easy way.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Vipul03
Starting Member

5 Posts

Posted - 2009-04-30 : 01:16:02

quote:
Originally posted by DonAtWork

So what if you repeat EmpID. Its not repeated in the Employee table. It is a FOREIGN KEY. That is its EXPRESS PURPOSE. Fred is trying to show you the correct way, not the easy way.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

I understand what Fred is saying but even then i will have to create 2 rows for same employee if he/she have 2 mobile no. associated with him/her.
I really don't want to repeat the row for same employee because 1 mobile no can only be associated with one employee so repetition will not be worth.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-30 : 07:04:21

Yes. If an employee had 2 mobile numbers, he will have 2 entries in the table. Welcome to relational data. You don't WANT to repeat it, but that is the CORRECT way to do it.

Therefore, what you are saying is, you do not want to do it the right way. Perhaps we do not understand what you are asking. Follow the first link in my signature, and restate your question with the appropriate data, and you may get a better answer.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-30 : 08:00:27

Maybe you shouldn't be using a relational database, a text file will give you exactly what you want.

KH
[spoiler]Time is always against us[/spoiler]

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-30 : 08:05:00

You can have separate columns for normal, mobile, and fax however, do not store "multiple" values using a delimiter in a column because that is bad design. If you need to store multiple normal, mobile, and fax numbers, then normalization is good.

Vipul03
Starting Member

5 Posts

Posted - 2009-04-30 : 08:55:00

Thanks all

You guys are right normalization is going to work and relational database is quite good. I know the power of relational database to some extent. But still i would like to figure out the way to add multiple values under one column.

Actually the problem is that say you have employees table with a mobile no. field. Then I should be able to add 2 no. to same column say 98352161,956316452 under one column by comma or semicolon seprated and when i make a query saying
select * from employees where mobile=98352161
such that it will show me the line (mean it will treat them individually)
Also I don't want to change the syntax of query to match regular expression

IS there is any way to do this

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-30 : 09:07:59

Why don't you normalize then? It will show you the row too.

SELECT * FROM [employees] INNER JOIN [contact_information] ON [employees].employee_id = [contact_information].employee_id WHERE [contact_information].contact = '98352161'

With this design, you can also store email, fax, phone etc.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-30 : 09:08:36

[code]
select * from employees e
join phone p on p.empId = e.empId and p.mobile='98352161'
[/code]