In short : database should enforce constraints. Why : Easier. For ex. to have a constraint set on a particular data column there is only one place to set it : the column itself. The data might come from various sources but the check is put where the data is finally put to rest. Integrity. … Read more
I do agree with posters below that Options 3, 4, or 5 are most likely to be appropriate. However, each of your suggested implementations has its benefits and costs. I’d suggest choosing one by matching it to your specific requirements. For example: Option 1 pros: Fast to implement. Allows DB actions on custom fields (searching, … Read more
Nulls are negatively viewed from the perspective of database normalization. The idea being that if a value can be nothing, then you really should split that out into another sparse table such that you don’t require rows for items which have no value. It’s an effort to make sure all data is valid and valued. … Read more
You’ll definitely need to optimize for the queries you’re doing. Here’s my best guess based on your description. You’ll probably want to know all Credit Cards for each Customer, so keep an array of those within the Customer Object. You’ll also probably want to have a Customer reference for each Payment. This will keep the … Read more
Normalization is basically to design a database schema such that duplicate and redundant data is avoided. If the same information is repeated in multiple places in the database, there is the risk that it is updated in one place but not the other, leading to data corruption. There is a number of normalization levels from … Read more
Your understanding is correct. You would do this in many cases. One example is in a relationship like OrderHeader and OrderDetail. The PK in OrderHeader might be OrderNumber. The PK in OrderDetail might be OrderNumber AND LineNumber. If it was either of those two, it would not be unique, but the combination of the two … Read more
No There is nothing wrong with Nullable FKs. This is common when the entity the FK points to is in a (zero or one) to (1 or many) relationship with the primary Key referenced table. An example might be if you had both a Physical address and a Mailing address attribute (column) in a table, … Read more
One method that is used by a few wiki platforms is to separate the identifying data and the content you’re auditing. It adds complexity, but you end up with an audit trail of complete records, not just listings of fields that were edited that you then have to mash up to give the user an … Read more
You have at least these five options for modeling the type hierarchy you describe: Single Table Inheritance: one table for all Product types, with enough columns to store all attributes of all types. This means a lot of columns, most of which are NULL on any given row. Class Table Inheritance: one table for Products, … Read more
Supporting historical data directly within an operational system will make your application much more complex than it would otherwise be. Generally, I would not recommend doing it unless you have a hard requirement to manipulate historical versions of a record within the system. If you look closely, most requirements for historical data fall into one … Read more