Geek Answers Handbook

SQL: database normalization while maintaining constraints

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 present here: suppose we have a list of species such as cat, dog, human, etc. We also have a set of properties (abbreviated details so that I can fit on the diagram more easily) that apply to some, but not necessarily all species - for example, the length of the tail (for species with tails), eye color (for eyes with eyes) etc.

SpeciesProps - , , - ​​ {, }, {, }, {, }, {, }, {, }. {Human, Tail Length}, , , .

"" "" . , {, }, {, } {, }.

: OrganismPropsValues ​​ "" - , , {, , ). {Rufus, , } {Rufus, , 20} ( ). , , , , , {Bob, Tail Length, 10}, {Human, Tail Length} SpeciesProps. , , SpeciesProps OrganismPropsValues, ?

+5
4

Entity-Attribute-Value antipattern. , .

:

  • , , .

  • , , . 1 1 , .

     ____________________             ____________________
|     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)    |
|....                  |
|______________________|

, , :

  • SQL , , varchar .
  • .
  • (.. NOT NULL) .
  • .
  • .

. - SQL SQL Antipatterns: .

+4

...
:
SpeciesPropsId SpeciesProps.
PropId SpeciesPropsId OrganismPropsValues.
.
SpeciesProps OrganismPropsValues ​​constrain.
OrganismPropsValues ​​ .

PropId OrganismPropsValues, , .

+2

, , , .

, OrganismId, SpeciesId OrganismSubId ( OrganismId, - ).

, :

ER Model

, SpeciesId "" . , , " " , "" .

, . (, SpeciesName SpeciesId PK). , JOIN ( ).

+2

PK Organism OrganismId No. PK (SpeciesId, No). , "Bob" (Human, 1), "Rufus" (Dog, 1) ..

OrganismPropsValues SpeciesId No ( OrganismId.)

FK OrganismPropsValues Props SpeciesProps:

     ____________________             ____________________
    |     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) |                 |
   |______________________|                 |
              ∞                             |
              |                             |
              -------------------------------
+1
source

More articles:


All Articles