Should I have 1 or 2 tables for user personal addr

问题:

If I have users, as well as user groups (like a local astronomy group/club), and I want both to have a one to many relationship with street addresses, can I just have 1 address table, and two fk's so that I don't have to duplicate the table schema? or is it better practice to just have 2 separate tables, user_addresses & user_group_addresses? Appreciate your input and time, thx!

回答1:

The design you describe, with two foreign keys, is called an exclusive arc. Only one of the two foreign keys should be populated. It's pretty awkward to enforce and to use.

For instance, an address must reference one entity, so conceptually the column is mandatory and should be NOT NULL. But you can't make both columns NOT NULL, because one of them doesn't pertain to a given address. So they have to be nullable. And then you have to have some other way to prevent both from being NULL, and also prevent both from being non-NULL. MySQL doesn't support CHECK constraints, so you can write a trigger or else write custom application code to enforce this rule.

What about creating one address table, but reverse the relationship? That is, the Users and Groups tables contain a foreign key reference to the Addresses table instead of the other way around.

The other solution is for both Users and Groups to be dependent on a common supertable, call it "Addressables" or something. Like an interface or abstract class in OO design. Then your addresses can also have a foreign key to Addressables. See examples in other questions I have answered on this subject.

I also cover this problem in more detail in my book, SQL Antipatterns, Avoiding the Pitfalls of Database Programming, in the chapter "Polymorphic Associations".

回答2:

If the adresses for users and groups are actually the same type of adresses, got with one address table you can reference as needed then.

回答3:

It is much better to have 1 address table unless there are specific needs that one address type has over another. This is much easier to maintain and also allows you to add functionality. For instance, if your local astronomy group decides they want to have "events" somewhere then you just create the events table and reference the address table and you are good to go. If you break them out, then each time you have a new "entity" that has an address you will have to create a new table.

Hope this helps.

As to your comment: I would put the reference in a separate reference table.

回答4:

The first priority in database design is the effectiveness. There would be an implicit definition of whether an address belongs to a user or a group when using the one-table strategy to persist all the addresses.

Because you don't have to recognize which addresses are the desired set(users' or groups'), the two-table strategy is a easier way to program and to prevent somebody from writing wrong code(SQL).

For example, we need some data from users' addresses:

SELECT * FROM user_address WHERE <other conditions>; // The two-table strategy

/**
 * The one-table strategy
 */
SELECT * FROM all_addresses
WHERE user_id IS NOT NULL
    AND <other conditions>;

The performance, moreover, would be better if we use two tables to persist addresses whatever the addresses are needed.

In one-table strategy, perhaps the "IS NOT NULL" condition wouldn't be optimized even if there is an index in that column(depends on database systems). Join is another way to recognize the users' addresses in one-table strategy, but it still has more effort than another strategy.

The one-table strategy, however, has its performance benefit. If we need to collect all of the addresses(whatever users' or groups') and this kind of operation is the performance bottleneck of system, you may consider using one-table strategy.