tblEverything

[delikedi]

Hello,
I maintain a database that manages tasks. For the sake of familiarity, I'd like to explain my problem by using the classification of mammals:
http://4.bp.blogspot.com/-ECkkgAaI5eE/UBJuCcZH1WI/AAAAAAAAApU/w-H1Fgc_W-M/s1600/2-classification.gif

If I were to create a database to record information of all living things, I'd start with tblLivingThings, which would contain the only autonumber field in the entire database. Every other child table would have a 1-1 relationship with its master table.

When designing my database, due to lack of experience and info, I used distinct master tables for each task type I were to manage. In retrospect, with the above link in mind, this was like creating tblFelis, tblPanthera, tblCanis and tblUrsus and their child tables, but omitting tblCarnivora, tblMammalia... all the way up to tblLivingThings.

As time passes, the need to store information about upper classification levels arises. I'm sure experienced designers can immediately appreciate the burden of adding a supertype table to an existing database. Many queries, forms and reports have to be rewritten/restructured. The even greater problem is that the supertype tables that have to be added to the database have to "discovered" by myself, in time. There's no ready classification tree like the one above.

What would you recommend to a person in my position? The only advice I can give to myself is creating a tblEverything table, and relate all current master tables to it. And hope that not many intermediate supertypes have to be inserted between tblEverything and the current master tables.

 

[speakers_86]

Your example confused me a little. Is this how you are currently doing it?

If so your normalization is off. It should be more like this:

tblAnimals
AnimalID              PK autonumber
AnimalName   
FamilyID               FK
GenusID               FK
SpeciesID             FK
CurrentStatusID     FK
Other relevant data

tblFamily    
FamilyID                PK autonumber
FamilyName

tblGenus
GenusID                 PK autonumber
GenusName

tblSpecies
SpeciesID                PK autonumber
SpeciesName

tblCurrentStatus      is the animal alive or dead
CurrentStatusID      PK autonumber
CurrentStatus

 

[delikedi]

Hello speakers_86, and thank you for your interest.

One of my master tables is:
tblProcurements
ProcurementID
ProcurementType
Status
AssignedTo
etc.

This table is a supertype, and has 1-1 relationships with subtype tables, namely tblGoodsProcurement, tblServicesProcurement, tblConstructionProcurement. These subtype tables hold only specialized info about the procurements. The animal kingdom analogy to this structure would be tblPanthera as supertype (procurements) with child tables (subtypes) tblTigers, tblLions and tblJaguars. An african jaguar for example, would obtain its PK and "EndangerednessStatus" from tblPanthera, but get its jaguar-specific properties from tblJaguars.

Another such structure exists for supertype tblProjects, with tblBuildingProjects and tblInfrastructureProjects as child tables. It could correspond to tblFelis, with child tables tblDomesticCats and tblWildCats.

Now I am asked to record budget allocations. Budget is allocated for both procurements and projects. Therefore the proper way to do this is by constructing "tblBudgetEaters", which would correspond to tblFelidae. I've had to do this before, the recipe is as follow:
1. Convert the autonumber PK fields of tblPanthera and tblFelis to normal longs.
2. Reindex the PK field of one of them if necessary, so no collision occurs at step 3.
3. Construct tblFelidae, and append the PK values of both tblPanthera and tblFelis to it, using an append query.
4. Establish 1-1 relationships between the new mother and her children.
5. Rewrite almost all queries that had something to do with those tables.
6. Pray that it is the last time you have to do it.

I take the time and effort to do it this way, since I am led to believe this is is good normalization practice. I also see for myself the benefits: No extra FK field in tblBudgets. No band-aid code to decide which type of budget for which type of entity. No extra decider fields in queries.

My advice to myself, which is the construction of tblEverything, would save me steps 1 and 2. It might also save some work at step 5. My main reason for posting was to ask around what you guys do at times like these.

 

[speakers_86]

Strictly speaking of you analogy, the will not be a new table for each specific family (or genus or whatever). You mentioned tblTigers, there would be no such entity. Look at my last post again. The tables tblFamily and tblGenus would merely list all families or genuses. The table tblAnimals would list ALL animals, and describe each animal via foreign keys to tblFamily and tblGenus.

Note- Looking over my last post, it seems tblSpecies probably is not needed, because each record in tblAnimals would be a new species.

 

[kipcliff]

tblSpecies would be necessary to hold the information relevant only to a given species and not necessarily appropriate to the higher levels of classification.

If you are using tblAnimals to represent individual members, tblSpecies is a part of the analogy.


However, my only experience with 1-to-1 relationships had to do with security issues, not with good database design. The animal classification analogy uses 1-to-many relationships.

Perhaps it is time to review your database design from the perspective of your experience, and consider a fresh rebuild?

 

[delikedi]

thank you for your input, kipcliff.

I agree that the animal kingdom taxonomy would have one-to-many relationships. Complementary to speakers_86's suggestion, it would be something like:

tblAnimals
AnimalID PK autonumber
AnimalName
SpeciesID FK

tblSpecies
SpeciesID PK autonumber
GenusID FK
SpeciesName

tblGenus
GenusID PK autonumber
FamilyID FK
GenusName

tblFamily
FamilyID PK autonumber
OrderID FK
FamilyName

However, I find this structure applicable only for "classes" of animals. I don't find it suitable for animals themselves.

Consider that you are a zoo keeper and you keep track of, among many other things, the fur style of big cats. You would create a field named "FurStyle" in tblAnimals. A leopar would be assigned "short fur with spots". Another leopard could receive "short, unicolor fur" (I'm just making these up btw). These are species-level properties of the same type of animal. However this field would not be applicable to animals who don't have fur. If you consider the countless other properties of other animals, tblAnimals would have far more fields than the allowed number of 255 to record that information. And almost all of them would be inapplicable to most of the animals, that afaik would upset the gods of normalization

 

[delikedi]

I'd like to share a snapshot of a situation from the actual database. A typical record for a "Construction Procurement" is spread across the following tables that have 1-1 relations with each other in the order given:

tblEverything
ThingID (Autonumber PK)
ThingName
ActivenessStatus
EntryDate etc.

tblBudgetEaters
BudgetEaterID (inherited from table above)
BudgetEaterType
AllocatedBudgetCode
AllocatedAmount

tblProcurements
ProcurementID (inherited from table above)
ProcurementType
Proc.SessionDate
Proc.MeetingDate etc.

tblConstructionProcurements
ConsProcID (inherited from table above)
SiteID
ConstructionCoefficient
Const.ClassID etc.

The top level table branches into at least 4 tables, one being tblBudgetEaters. BudgetEaters branch into two tables: tblProcurements and tblProjects. Both of them bite the budget. If I don't establish tblBudgetEaters, I would have to insert the fields BudgetEaterType, AllocatedBudgetCode, AllocatedAmount in both of these tables, which I consider redundant.

The same goes for procurements. I have 3 tables for each type of procurement, but they all share common fields such as Proc.SessionDate or Proc.OfficialCode. Instead of repeating these fields in all three tables, I merged them into a supertype table, namely tblProcurements. The fields that remain in tblConstructionProcurements are not applicable to tblGoodsProcurement.

I must say that I don't have a special devotion to 1-1 relationships, and they are no more than %10 of all relationships in the database. However when relating the top-level tables such as those above, I found them to fit like a glove. It makes a lot of sense to me to merge entities that share some characteristics into supertype tables.

I agree with you that tblAnimalClasses can be built with like 10 tables in total. The would be linked with 1-to-many relations with each others.
But if we were to build tblAnimals and not tblAnimalClasses, we might need, say, 10^10=100 tables. I'm just trying to emphasize the difference.

 

[Galaxiom]

My table structure would be:

tblClassificationType: ClassificationTypeID, ClassificationTypeName.
Records for Kingdom, Phylum, Order, Family, Genus, Species etc

tblClassification: ClassificationID, ClassificationTypeID, ClassificationName.
Records: Archea, Chordate, Mammal, any name of any group of any system of classification.

This structure supports any number of new groupings such as the Clade structure.
All proposals by previous posters would only accommodate this by adding tables and would immediately run into relationship problems.

These problems are avoided in my proposal by using a junction table to identify the relationships between Classifications. The table would have two fields ParentClassification and ChildClassification. Any Classification can be recorded as a child of any other classification as would be necessary where the Clade and Classic nomenclature intersect.

The Classification table can not only hold general information about a phylum or genus, it can actually be the main species table where detailed information about the organism is held in exactly the same structure. A Species has a parent Genus and would be connected in the junction table just like any other level.

Do think about it. This scheme is completely expandable and only requires three tables.

 

[kipcliff]

I see where you're at, I believe. It is like a library database, where different types of media have properties that do not apply to others. Books have pages, but CD's and videos do not. One combines what is common in one table with an item ID, and creates 1-to-1 relations with tables designed to hold media specific data.

This has been fun, but I need to get some sleep before breakfast.

 

[Galaxiom]

It would also support pending classification where a pending classification could have any number of junction table entries to show all alternative proposals.

Indeed multiple versions of the cladistic system or any other system with variable numbers of layers could be simultaneously supported.

Even the ad hoc classifications wanted by OP. Furry animals can be added to the Classifications and connected to multiple species via the join table. Spotted, striped etc.

Finer graduation of classifications is supported to any level of graduation. Morover the same child classification can be used against multiple parent classifications while the junction table makes sense of what would otherwise be a spaghetti of relationships.

For example "two" could be a subclassification of "eyes" or "legs". The query to find the animal can be directed by presenting the junction information to the searcher in cascading comboboxes.

 

[delikedi]

@ Galaxiom
In the same database, I once faced a situation where I needed to store information about several types of buildings. Information for ten types of buildings required a master "Structures" table, and ten child tables, each having fields exclusive to that building type; for example "tblBridges" had a field named "Span" and "tblBuildings" had "StoreyCount". After struggling with it for a while, I had given up on that table structure, and instead built three tables. One was named tblStructureTypes, one was tblStructureProperties, and the last one was a junction that assigned certain properties to certain structure types. This trio allowed me to flexibly assign properties to any new or existing structure record.

Although that part of the database is only half-finished due to my inability to wrap my mind around it, I very much suspect that you are talking about something very similar, if not the same. It's like joining a table of tables with a table of fields. I'm pretty excited now that the same issue is touched. I'll report back as soon as I inspect this further.