Suppose I have the following tables:
____________________ ____________________
| Organisms | | Species |
|--------------------| |--------------------|
|OrganismId (int, PK)| |SpeciesId (int, PK) |
|SpeciesId (int, FK) |∞---------1|Name (varchar) |
|Name (varchar) | |____________________|
|____________________| 1
1 |
| |
| |
∞ ∞
______________________ ____________________ _______________
| OrganismPropsValues | | SpeciesProps | | Props |
|----------------------| |--------------------| |---------------|
|OrganismId (int, FK) | |PropId (int,PK,FK) | ∞-----1|PropId (int,PK)|
|PropId (int, FK) | |SpeciesId(int,PK,FK)| |Name (varchar) |
|Value (varchar) | |____________________| |_______________|
|______________________| 1
∞ |
| |
-----------------------------------------------------------
A quick explanation of what I am trying to represent here: suppose we have a list of species, such as cat, dog, human, etc. We also have a set of properties (abbreviated Props so I could fit it more easily in the diagram) which apply to some but not necessarily all species--for example, this may be tail length (for species with tails), eye color (for those with eyes), etc.
SpeciesProps is a linker table that defines which properties apply to which species-- so here we would have {Human, Eye Color}, {Dog, Eye Color}, {Cat, Eye Color}, {Dog, Tail Length}, {Cat, Tail Length}. We do not have {Human, Tail Length} because Tail Length is obviously not a valid property to apply to a human.
The Organisms table holds actual "implementations" of the species-- So here we might have {Human, Bob}, {Dog, Rufus}, and {Cat, Felix}.
Here is now my issue: in the OrganismPropsValues table, I want to store the 'values' of the properties for each organism--so for example, for Bob I want to store {Bob, Eye Color, Blue}. For Rufus, I would want to store {Rufus, Eye Color, Brown} and {Rufus, Tail Length, 20} (similar for Felix). My problem however, is that in the schema that I have detailed, it is perfectly possible to store {Bob, Tail Length, 10}, even though the {Human, Tail Length} tuple does not exist in SpeciesProps. How can I modify this schema so I can enforce the constraints defined in SpeciesProps in OrganismPropsValues, while maintaining adequate normalization?
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:
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 Volume 1: Avoiding the Pitfalls of Database Programming.