用于管理地址的数据库模式 [英] Database schema for managing addresses
问题描述
您有一些选择：
在地址表中添加userid列。在用户表中，添加addressid列。
用户表会告诉您用户已经显示的当前地址。
select * from用户u
内部加入地址a对u.userid = a.userid和u.addressid = a.addressid
其中userid = 45
地址表会告诉你用户使用的所有地址。
select * from userid = 45
优点是用户表指向你最近的地址表。当用户添加新地址时，地址表获取插入，用户表更新。缺点是在地址表中重复使用用户名 - 如果父母和子女拥有相同的地址（假设每个用户都是用户），则应为每个用户多次添加相同的地址。要获取当前地址，您必须同时询问用户和地址表。
在地址表中，添加userid列和isPrimary位列。在用户表中不要添加addressid列用户表不会告诉你有关地址的任何信息，但地址表会告诉你用户使用的所有地址哪一个是他们的主要。
select * from用户u
内部加入地址a对u.userid = a .userid和a.isPrimary = 1
其中userid = 45
您可以得到当前地址
select * from address where userid = 45 and isPrimary = 1
地址表，就像前面的例子一样，会告诉你用户使用的所有地址。
好处是地址表负责告诉你这个地址属于谁，是否是主要的。
将用户存储在用户表中，地址在地址表中。创建交汇点表，将地址和用户放在一起
UserAddress表可以创建具有userid，addressid，isPrimary列的用户。用户表只会包含用户信息。地址表将有地址，并知道哪个地址属于哪个用户，你会问UserAddress表。
select * from user u
内部连接useraddress ua u.userid = ua.userid和ua.isPrimary = 1
内部连接地址a on ua.addressid = a.addressid
其中u.userid = 45
这个优点是将地址与多个人联系起来。同一个家庭的父母和两个孩子可以使用相同的地址。如果家庭移动到另一个地点，则在地址表中有一个附加记录，在联结表中有四个记录。缺点是需要额外的连接。
选择适合您情况的用例。
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 and phone number. I am using a table to store address and its id is stored in users table. Now as people can have change address so I'm not able to understand it how to manage it. I want to keep the old address as well as new for new users. Can anybody help?
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 * from user u
inner join 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 * from user u
inner join address a on u.userid = a.userid and a.isPrimary = 1
where userid = 45
You can get current address for a given user directly from address table.
select * from address where userid = 45 and 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 * from user u
inner join useraddress ua on u.userid = ua.userid and ua.isPrimary = 1
inner join 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.
这篇关于用于管理地址的数据库模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
