Database Schema For Managing Addresses

I am developing a MVC project with sql server. in which each user will be given a username after registration. Registration is done using various data like address and email number

Solution 1:

You have some options:

In address table, add userid column. In user table, add addressid column.

User table will tell you the current address user has indicated.

select*fromuser u
innerjoin address a on u.userid = a.userid and u.addressid = a.addressid
where userid =45

Address table will tell you ALL the addresses user has used.

select * from address where userid = 45

The advantage is that user table points you to the recent address table. When user adds new address, address table gets an insert and user table is updated. The disadvantage is repetition of userid in address table - if parents and children have the same address (assuming each individual is a user), you'd be adding the same address multiple times for each user. To get current address, you will have to ask both user and address tables.

In address table, add userid column and isPrimary bit column. In user table do NOT add addressid column

User table will not tell you anything about address, but address table will tell you all the addresses the user has used and which one is their primary.

select*fromuser u
innerjoin address a on u.userid = a.userid and a.isPrimary =1where userid =45

You can get current address for a given user directly from address table.

select * from address where userid = 45and isPrimary = 1

Address table, just like in the previous example, will tell you ALL the addresses user has used.

The advantage is just that address table is responsible for telling you who that address belongs to and whether it is primary.

Store user in user table, address in address table. Create junction table to bring address and users together

UserAddress table can be created that has userid, addressid, isPrimary columns. User table will only contain user information. Address table will have addresses and to know which address belongs to which user, you'd ask UserAddress table.

select*fromuser u
innerjoin useraddress ua on u.userid = ua.userid and ua.isPrimary =1innerjoin address a on ua.addressid = a.addressid
where u.userid =45

The advantage of this is associate an address with multiple people. Parents and 2 children residing in the same household can be associated with the same address. If household moves to another location, you have one additional record in address table and 4 records in junction table. The disadvantage is an extra join.

Choose the use case you feel appropriate for your situation.