#1. It took me a minute or two to find the problem. In it's current form, your WallContacts table requires one record per person per wall. So, if Joe is the owner of a building with 2 walls, he will need 2 records in WallContacts (one for each wall). This is because you're storing the WallID in that table.
Try this: Remove the WallID from WallContacts. Create a new table:
Table: Walls_vs_Contacts (I can't think of a better name)
ContactID (FK)
WallID (FK)
This table will serve as the go between between WallContacts and WallsMaster. So when creating queries, you join WallsMaster to Walls_vs_Contacts to WallContacts.
#2. A less important issue is that your WallInteraction doesn't include ContactID. Also, WallInteraction table can't properly record interactions between the staffmember and 2+ people. If this is an issue worth fixing (that's up to you to decide), you'd have to make an additional many-to-many table like in #1:
Table: Walls_vs_Contacts (I can't think of a better name)
ContactID (FK)
InteractionID (FK)
#3. Depending on how many staff members you have, you might want to make a table with a StaffID and StaffName fields. Otherwise, WallInteractions.Staffname will fill up with "Sheryl","Sherri","Sherryl LastName","S. Lastname", etc. making it impossible to search for all interactions involving her.
Otherwise, you're off to a solid start. I like how you identified and properly named the unique keys in advance. (Oh, and if you think I'm wrong, I probably am.)
Here is a suggestion so you can enforce the constraints you want declaratively. (I've simplified the table names a bit, removed the bridge_ prefix.)
We remove footnote_num from:
Table: a_ref -- was named: bridge_a_reference
a_id,
ref_id
Primary key:
(a_id, ref_id)
Foreign keys:
a_id -> a
ref_id -> reference
We add this table - which will basically store only those rows from a_ref with footnote, those you want to add children into the b_ref:
Table: a_ref_with_footnote
a_id,
ref_id,
footnote_num
Primary key:
(a_id, ref_id)
Unique key:
(a_id, footnote_num)
Foreign keys:
(a_id, ref_id) -> a_ref
And finally the 3rd table stays as in your design except the foreign keys which now reference the intermediate table (a_ref_with_footnote):
Table: b_ref -- was named: bridge_b_reference:
a_id,
b_id,
ref_id,
Primary key:
(b_id, ref_id)
Foreign keys:
(a_id, b_id) -> b
(a_id, ref_id) -> a_ref_with_footnote
Best Answer
You should go as far as you should, and no further. Of course. ~ The problem may be that this is a bit of an art, and it's why this isn't a pure science.
Our main product is an analysis and reporting system, and so in that regard, we have quite a few detail records. We initially had it designed with lots of joins on a common ID for some of the child records, but we found that if we denormalized a couple of fields we could cut out a LOT of joins and we could take away a lot of performance headaches.
But we only knew that because we 1) created a "normalized" design, 2) started using it, 3) profiled the actual performance after hundreds of millions of rows across dozens of tables.
The end story is that until we profiled we couldn't know for sure what was going to work for us. We liked the idea of normalizing so we could update more easily, but in the end actual performance was the deciding factor. That's my advice for you: Profile, profile, profile.