Sql: Normalization Of Database While Retaining Constraints

Suppose I have the following tables: ____________________ ____________________ | Organisms | | Species | |-------------------

Solution 1:

You're implementing the Entity-Attribute-Value antipattern. This can't be a normalized database design, because it's not relational.

What I would suggest instead is the Class Table Inheritance design pattern:

• Create one table for Organisms, containing properties common to all species.

• Create one table per species, containing properties specific to that species. Each of these tables has a 1-to-1 relationship with Organisms, but each property belongs in its own column.

  ________________________________________
  |     Organisms      |           |       Species      |
  |--------------------|           |--------------------|
  |OrganismId (int, PK)|           |SpeciesId (int, PK) |
  |SpeciesId (int, FK) |∞---------1|Name (varchar)      |
  |Name (varchar)      |           |____________________|
  |____________________|
            1
            |
            |
            1
   ______________________ 
  |    HumanOrganism     |
  |----------------------|
  |OrganismId (int, FK)  |
  |Sex      (enum)       |
  |Race     (int, FK)    |
  |EyeColor (int, FK)    |
  |....                  |
  |______________________|
  

This does mean you will create many tables, but consider this as a tradeoff with the many practical benefits to storing properties in a relationally correct way:

• You can use SQL data types appropriately, instead of treating everything a free-form varchar.
• You can use constraints or lookup tables to restrict certain properties by a predefined set of values.
• You can make properties mandatory (i.e. NOT NULL) or use other constraints.
• Data and indexes are stored more efficiently.
• Queries are easier for you to write and easier for the RDBMS to execute.

For more on this design, see Martin Fowler's book Patterns of Enterprise Application Architecture, or my presentation Practical Object-Oriented Models in SQL, or my book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

Solution 2:

Hmm... Here is one way to do it: Add SpeciesPropsId into SpeciesProps table. Replace PropId with SpeciesPropsId in the OrganismPropsValues table. You will need to change constrains a bit. Need to add SpeciesProps to OrganismPropsValues constrain. Need to remove OrganismPropsValues to Props constrain.

Technically you do not have to remove PropId from OrganismPropsValues, but if you keep it it will make data redundat.

Solution 3:

Whenever you have a diamond-shaped dependency like this, consider putting more emphasis on composite PRIMARY KEYS.

Specifically, identify the Organism not just by OrganismId, but by the combination of SpeciesId and OrganismSubId (you can still have OrganismId, but keep it as an alternate key - not show here for brevity).

Once you do that, your model can be made to look like this:

The key thing to note here is that SpeciesId is "propagated" down both edges of this diamond-shaped graph. This is what gives you the desired restriction of not being able "assign a value" to a property that was not "declared" for the given species.

BTW, use singular when naming your tables. Also, consider using natural primary keys (e.g. SpeciesName instead of SpeciesId as PK) - if done right it can significantly increase the speed of your JOINs (especially in conjunction with clustering).

Solution 4:

Another way to achieve these constraints would be to change the PK of Organism table by dropping OrganismId and adding a No. Then make PK the compound (SpeciesId, No). So, "Bob" would be (Human, 1), "Rufus" would be (Dog, 1), etc.

Then, add in the OrganismPropsValues table, the SpeciesId and the No (removing the OrganismId.)

This will allow to change the FK from OrganismPropsValues to Props to reference SpeciesProps instead:

     ____________________             ____________________
    |     Organisms      ||       Species      ||--------------------|           |--------------------||SpeciesId (int, FK) ||SpeciesId (int, PK) ||No (int)            |∞---------1|Name (varchar)      ||Name (varchar)      ||____________________||PK (SpeciedId,No)   |1|____________________||1|||||
              ∞                                 ∞
    ______________________        ____________________          _______________
   | OrganismPropsValues  ||   SpeciesProps     ||     Props     ||----------------------|      |--------------------|        |---------------||SpeciesId (int, PK)   ||PropId (int,PK,FK)  | ∞-----1|PropId (int,PK)||No (int, PK)          ||SpeciesId(int,PK,FK)||Name (varchar) ||PropId (int, PK)      ||____________________||_______________||Value (varchar)       |1|FK (SpeciesId,No)     |||FK (SpeciesId,PropId) |||______________________||
              ∞                             |||-------------------------------