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

i came upon rather interesting situation need guidance me design database schema follows "best practises" or done "the recommended way".

my dilemma follows:

i have event table basic properties such id, name, date etc. needs address info straight forward way extend table fields such street, city, country etc. have user table needs store address data. right thing create third table called address , set relationships between address/user , address/event. tricky part. table should hold primary key/foreign key.

1. one way extend table address columns such eventid , userid. tables event , user "parent" table , address "child" table. address table hold foreign keys 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 see such design every row addresstable contain unnecessary null field. example if address specifies user address column eventid null , same goes if address row specifies event address column userid null.

   second drawback anytime add new table needs connected address table need add column table address reference new table's primary key.

2. second possibility extend tables event , user address's primary key column foreign key in relationship.

   |eventtable:|  |usertable: | |addresstable| |           |  |           | |            | |eventid pk |  |userid pk  | |addresid pk | |name       |  |name       | |street      | |othercolumn|  |othercolumn| |city        | |addressid fk| |addressid fk|                      

   everything perfect solution except have doubts when enable cascading delete on foreign keys. me natural way of thinking when remove event or user database i'd have addresses removed well. in such design address table parent , user/event children. when remove address entry cascading deletes enabled remove event/user entry. logically doesn't make sense me. should other way around, , problem i'm unable solve. perhaps second design acceptable , i'm confusing myself no reason.

ideally i'd love come such design enabling cascade deletes first remove event or user , address deleted automatically.

i know there third option of joint tables that's many many relationships , if user/event should contain single address.

thanks!

for reasons give option 1 no-go.

with option 2 should not worry unused address records. in fact, might turn useful during creation of new events or users, provide search facility in address "database". taken further, decide pre-fill address table data downloaded address provider. search facility become useful.

once plan have big address list, might want break address it's own hierarchy: street belongs city, city belongs country. of course, in practice street can shared several cities, , decide set n-to-n relationship there, or opt n-to-1, have (but in practice little) duplication of streets.

as can see, can taken far, , lead more effort in writing code around manage all.

if on other hand not interested in keeping unused addresses, manage via delete triggers on event , user tables, check if related address has become orphaned, , if so, deletes it. however, should not important happen @ same time, risk delete operation might take longer execute or fail, affecting user experience. better asynchronously , let scheduled job clean-up once week or so.