CODEWITHSUNDEEP
phpmysqldatabasephpmyadmin
Mahesh
Mahesh

Reputation: 55

How can I add multiple addresses of single user in MySQL database

i need help. I want to have two tables in database. One is USER n othetis ADDRESS. I want to add multiple addresses for the user that is added in USER table. How can i do this ? Thank you.

Upvotes: 0

Views: 4040

Answers (5)

Robby Rahmana
Robby Rahmana

Reputation: 29

you only need to create one table called ADDRESS:

CREATE TABLE ADDRESS(id int not null,user_id int,address varchar(255),PRIMARY KEY (id),FOREIGN KEY (user_id) REFERENCES USER(user_id));

Upvotes: 0

Momus
Momus

Reputation: 394

The ADDRESS table should have a many-to-one relationship with the USER table and contain USER_ID as a foreign key. Simply create multiple ADDRESS records that all have the same USER_ID. To differentiate them you should add a field to specify the type of address such as home, business, etc.

USER
ID, NAME, ...

ADDRESS
ID, USER_ID, ADDRESS_TYPE, STREET, ...

You should also really create separate tables for ADDRESS_TYPE, CITY, COUNTRY, STATE, etc. to avoid unnecessarily duplicated data and the problems that occur when those fields are allowed to be just entered as text - e.g. Montreal, montreal, MONTREAL, mtl, Montréal are all valid ways people write the same city.

Upvotes: 0

JYoThI
JYoThI

Reputation: 12085

1st : You can add user unique id in address table .

2nd : And also maintain auto_increment primary key in address table why means when your going to edit the address means you need to update the right one so it's useful in future so maintain one column for that .

Upvotes: 3

B. Desai
B. Desai

Reputation: 16446

You Have two different tables:

1) USER

2) ADDRESS

Give foreign key for ID of user table to address table

Example:

USER

id    name    email
1     abc     [email protected]

ADDRESS

id    user_id      address detail
1      1          address detail 1
2      1          address detail 2

Here user 1 has 2 address. You can add more address also

Upvotes: 2

Kaushali de silva
Kaushali de silva

Reputation: 70

According to the my opinion its just simple. Its one to many relationship of two tables. you can set user id as a foreign key of address table and add addresses. user id is the primary key of user table.

Upvotes: 0

Related Questions