What is the best solution to design sql table relationship for a table that will be used by other tables

Question

I came upon a rather interesting situation where I need guidance to help me design my Database schema that follows "best practises" or is done "the recommended way".

My dilemma is as follows:

I have an Event table with basic properties such as Id, Name, Date etc. It needs an address info so the most straight forward way would be to extend the table with fields such as street, city, country etc. Well I also have a User table that also needs to store address data. So the right thing to do would be to create third table called Address and set up relationships between Address/User and Address/Event. This is the tricky part. Which table should hold primary key/foreign key.

1. One way to do is to extend table Address with columns such as EventId and UserId. So tables Event and User would be the "parent" table and address would be the "child" table. The Address table would hold foreign keys to User/Event's Id primary keys.

   |EventTable:|  |UserTable: | |AddressTable|
   |           |  |           | |            |
   |EventId PK |  |UserId PK  | |AddresId PK |
   |Name       |  |Name       | |Street      |
   |OtherColumn|  |OtherColumn| |City        |
                                |EventId FK  |
                                |UserId FK   |
   

   Two drawbacks that I see from such design is that for every row AddressTable would contain extra unnecessary Null field. For example if address specifies user address then column EventId would be Null and same goes for if address row specifies Event address then column UserId would be Null.

   Second drawback is that anytime I add a new table that also needs to be connected to the address table then I would need to add another column to table Address that would reference the new table's primary key.

2. Second possibility is to extend tables Event and User with Address's primary key column so they would be the foreign key in the relationship.

   |EventTable:|  |UserTable: | |AddressTable|
   |           |  |           | |            |
   |EventId PK |  |UserId PK  | |AddresId PK |
   |Name       |  |Name       | |Street      |
   |OtherColumn|  |OtherColumn| |City        |
   |AddressId FK| |AddressId FK|                     
   

   Everything would be perfect with this solution except that I have doubts now when I enable cascading delete on Foreign keys. To me natural way of thinking is that when I remove an Event or User for database I'd like to have their addresses removed as well. But in such design the address table is the parent and User/Event are the children. So when I remove Address entry with cascading deletes enabled I would also remove Event/User entry. Logically it doesn't make too much sense to me. It should be the other way around, and this is the problem I'm unable to solve. Perhaps the second design is acceptable and I'm just confusing myself for no reason.

Ideally I'd love to come up with such design where by enabling cascade deletes I first remove Event or User and then their address would be deleted automatically.

I know that there is third option of joint tables but that's only for many to many relationships and what if User/Event should contain just a single Address.

Thanks!

Answer 1

For the reasons you give option 1 is a no-go.

With option 2 you should not worry about unused Address records. In fact, they might turn useful during the creation of new Events or Users, as you could provide a search facility in your address "database". Taken further, you could even decide to pre-fill the Address table with data downloaded from some address provider. Then the search facility would become very useful.

Once you plan to have a big address list, you might want to break an Address up into it's own hierarchy: a street belongs to a city, a city belongs to a country. Of course, in practice a street can be shared by several cities, and you could decide to set up an n-to-n relationship there, or you could opt for n-to-1, where you have some (but in practice very little) duplication of streets.

As you can see, this can be taken very far, and will lead to more effort in writing code around it to manage it all.

If on the other hand you are not interested in keeping unused addresses, you could manage this via delete triggers on Event and User tables, which would check if the related address has become orphaned, and if so, deletes it. However, this should not be that important to happen at that same time, with the risk that your delete operation might take longer to execute or even fail, affecting the user experience. It is better to do this asynchronously and let a scheduled job do the clean-up once a week or so.

Answer 2

Addresses are tricky, indeed.

First, address is an independent thing - its existence is beyond your control, rather it exists as long as its local council wants it to. Another important thing - addresses tend to be reused again and again, especially if we are talking about large events or short term rent accommodations.

Considering all that, it is clear that option 1 is just plain wrong and does not correlate to reality. The second is better, but still misses quite a lot, though in this case it depends more on how far you are willing to go.

For example, if you want to store history of address changes for any kind of entity, you will need history table(s) - again, there are several possible designs. You can make a single address history table with fields like:

AddressId (PK)
TenantId (PK)
StartDate (PK)
EndDate

, where TenantId will reference a supertype table which will be made a parent for all entities that can use addresses. Such a table (not the supertype one) will also help in preventing (or allowing?) of simultaneous use of the same address by more than 1 tenant at any given time.

And this is just the tip of the iceberg :)

Answer 3

The second method seems the cleanest to me. After all, you could have multiple users with the same address, for instance. I should note, however, that it is not clear that an "event" address and a "person" address are the same thing. For instance, a "person" address might have a postal code, and an "event" address might describe different ways to arrive at a location.

In any case, you have the cascading backwards. When you delete a user, for instance, you are thinking backwards. Nothing would happen to address. The question is what happens when you delete something from address. Then the corresponding users and events would be deleted. The purpose of cascades is to maintain relational integrity when primary keys change.

If you want to delete addresses when users/events are deletes, then I would suggest triggers. However, this is not necessary for relational integrity.

Answer 4

Joint tables are still an option, as long as you maintain unique constraints on both of the FKs. However, the second option is probably best overall. In order to get the deletes to function the way you intend, I would recommend setting up a trigger on delete from EventTable and UserTable.