I'm working on a Program that manages customers and their application packaging requests. I want to store the Information in a MS SQL Database and have different default values depending on the customer, because different customers have a different set of relevant or used values.
My Database has 2 relevant tables: Customer and Application. One Customer can have many applications (1:n Foreign key in Application) But each Customer also has exactly one set of Default values(1:1 Foreign key in Customer)
I could not find anyone who tried something similiar after some research and i have a really bad feeling about these two references. Is there a more elegant way to achieve one outstanding member on the N side of a 1:N relationship?
There are several approaches:
Your customer and the set of defaults is 1:1.
The customer with all other application entities is 1:n
- You might put the defaults directly into your customer table (easy and fast but not clean)
- You might define two tables with the same structure. One with non-nullable columns to define defaults and bind them 1:1 and the second as 1:n relation (You need a UNION query to put them together)
- You might use a marker in your application table to mark the "default" row (You need to make sure, that there is only one marked record)
- You might - which seems to be your current approach - set a FK-ID into your customer table to store the ID of the default row.
- My approach was: Put a rank column into your application table. You might set the combination of customerID and rank as unique... This makes you able to define one with the lowest rank as the default and - similiar to a cascading stylesheet - you can start with the highest and move backward until you've found one value other than NULL.