For question #1, what's going to happen in the single table design when (and it will happen) you end up with more than one emergency contact and more than one responsible party?
Some people would suggest a single table with one row per person whether it's a Customer, EmergencyContact, or ResponsibleParty where there's "Type" column to distinguish the 3 and an extra column to identify the related customer for the later 2. I'd probably NOT go that way. If EmergencyContact and ResponsibleParty rows are identical, perhaps a "Customer" and a "Contact" table would suffice. Just remember that you can have multiple phone numbers, emails, pages, whatever for contacts, as well, and all of those should probably be stored in a separate table instead of having a wad of null columns in a table. I'd likely have a "Bridge" table between Customer and Contact called "CustomerContact" consisting only of the IDs of customers and contacts. You'll end up with more of a snowflake than a star in that area.
For question #2, yes... I'd have an "Invoice_Header" and "Invoice_Detail" table. Also, seriously consider how you design the Invoice_Detail table. After a month or so of and invoice coming into existence, all changes and backorders will likely be resolved and nothing else about the invoice or its details will ever change again. So, planning for the future, instead of backing up what will become years of data that will never again change, plan on partitioning at least the Invoice_Detail table. My personal favorite is Partitioned VIEWs rather than Partitioned TABLEs because Partitioned TABLEs have a whole lot of "gotchas" when it comes to restores, especially if you need a partial restore for a development or test environment. They don't warn you of things like that in the documentation for Partitioned Tables... although they incorrectly "tip" that Partitioned Tables are better Partitioned Views... which I don't agree with.
But, as with all else in SQL Server, "It Depends" and these are just suggestions from an old dude that's been screwed by it all both ways at one time or another. Choose carefully. 😀
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)