Reputation: 644
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...
ALL THE ANSWERS ARE HELPFUL UNFORTUNATELY I CAN PICK ONLY ONE BUT ALL ANSWERED HELPED ME
Upvotes: 0
Views: 666
Reputation: 1
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
| 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
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
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)
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
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