Skip to content
Advertisement

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

Advertisement

Answer

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 |
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement