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.
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.
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!
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.