CODEWITHSUNDEEP
sqldatabasedatabase-designforeign-keyscreate-table
Waaaaat
Waaaaat

Reputation: 644

SQL Database Design with more than one data in same column

I want to create a database table which will have multiply information. One of these information is phones. How can I create a database table that inside the phones column will contain let's say sub-columns, because a user may have more than one phone and be stored in the same row for the same ID.

Below is an image of what I mean and how I want my table to be... enter image description here

ALL THE ANSWERS ARE HELPFUL UNFORTUNATELY I CAN PICK ONLY ONE BUT ALL ANSWERED HELPED ME

Upvotes: 0

Views: 666

Answers (4)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

SQL Fiddle

MS SQL Server 2017 Schema Setup:

create table my_user (
    ID  int primary key,
    FirstName varchar(255),
    LastName varchar(255),
    address varchar(max)
)
create table user_phones (
     ID int primary key,
     userId int references my_user(ID),
     countrycode int,
     phone varchar(max),
     type varchar(255)
)
INSERT INTO my_user(ID,FirstName,LastName,address) VALUES(1,'test','test','test');
INSERT INTO user_phones(ID,userId,countrycode,phone,type)VALUES(1,1,99,'000099900','mobile');
INSERT INTO user_phones(ID,userId,countrycode,phone,type)VALUES(2,1,99,'99900000','home');
INSERT INTO user_phones(ID,userId,countrycode,phone,type)VALUES(3,1,99,'000009999','fax');

Query 1:

select * from my_user u
left join user_phones p on u.ID=p.userId

Results:

| ID | FirstName | LastName | address | ID | userId | countrycode |     phone |   type |
|----|-----------|----------|---------|----|--------|-------------|-----------|--------|
|  1 |      test |     test |    test |  1 |      1 |          99 | 000099900 | mobile |
|  1 |      test |     test |    test |  2 |      1 |          99 |  99900000 |   home |
|  1 |      test |     test |    test |  3 |      1 |          99 | 000009999 |    fax |

Upvotes: 2

Himanshu
Himanshu

Reputation: 3970

That's a ER flow as has a phoneid in your case via primary key foreign key relation

       users(id (Pk), name, address, phoneid(fk))

       phones(userid(fk), phoneid (pk), phonenumber) 
       here userid and phoneid references users.phoneid and users.id

Upvotes: 2

GMB
GMB

Reputation: 222722

There is no notion of subcolumns in relational databases.

If you want to store a variable number of phones per user, one solution is to create a another table to store them as rows. The phones table should have a column to store the id of the user each phone relates to, with a foreign key constraint that ensures that the value contained in that column do exist in the users table.

So:

users(id, name, address)
phones(id, user_id, phone_no)

Typical DDL (MySQL syntax):

create table users (
    id int primary key auto_increment,
    name varchar(50) not null,
    address varchar(50)
);

create table phones(
     id int primary key auto_increment,
     user_id int not null,
     phone_no varchar(20) not null,
     foreign key (user_id) references users(id)
);

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You generally do not. You create another table. Say you have:

create table users (
    userId serial primary key,
    name varchar(255),
    . . .
);

Then you create a "junction" or "association" table:

create table userPhones (
     userPhoneId serial primary key,
     userId int references users(userId),
     phone varchar(32)
);

You don't specify the database. I used serial for the primary key because it is used by Postgres and easy to type.

Upvotes: 3

Related Questions