Is it OK to use PRIMARY KEYs from one table as NAMEs of other tables

database-design

My case:

I have many organisms (for example human, mouse and etc.) and a few models for each one. All models are independent from each other and I will never query them together.

Normally I will do it like that:

PK (organism, model) is FK in one big table with models rows. But I do not need have them in one place, because data under different (organism, model) are independent, so I will always query a sub set of that big table base at FK (organism, model).

My idea is use PK (organism, model) as a TABLE_NAMEs for many separated tables, each one contains independent data.
I am not proficient in DB, and I can not find how should I structured data like this. Can you help me? Is my idea OK? Should I solve it in other way? Where can I find any clues?

summarizing:

PK —> TABLE_NAME

Best Answer

If the data in each subset it truly independent and will never be reported upon together then modelling them as one table per entity type is perfectly valid. The fact that the table name will match a key in another table isn't something the database will care about - that table just becomes a list that tells the business logic what entity tables to expect to exist.

Even the "if you will never report on them together" can be relaxed to "if you won't need to report on them live together in an efficient manner" because there is the etl-to-datawarehouse option for offline analysis and less than efficient (but workable) options for ad-hoc reporting.

Of course the database layer is not going to be able to enforce the relationship between table name and data in another table: FKs can only be defined on data not schema elements.

I suspect that you don't want the data to be completely independent though. If I've understood you then what you describe is a classic example where simple table inheritance is applicable and what you want is something like:

                   Organisms            HumanModel1Details       
OrganismTypes      ===============      ==================
=============      OrganismID (PK) <-<- OrganismID (FK)
Organism (PK) <-<- Organism   (FK)  |   SpecificDetail1
Model    (PK) <-<- Model      (FK)  |        ...
                   CommonProperty1  |   SpecificDetailN
                        ...         | 
                   CommonPropertyN  | 
                                    | 
                                    |   RatModel4Details
                                    |   ================
                                    `<- OrganismID (FK)
                                        SpecificDetail1
                                             ...
                                        SpecificDetailN

Your business logic sill needs to know that Organism.OrganismType='Human' & Organism.Model='Model1' means that the details are in the table HumanModel1Details and the database can't help you enforce that. The common properties at very least include something like "display name" which will be used in your UI. Having OrganismTypes as a separate table is optional but helpful; the database can then use the FK in Organism enforce that each has a valid combination from those defined in OrganismTypes, and you can add properties to each type too (perhaps properties, or just a free text field, describing what differentiates it from other models.

You may wish to consider splitting by just Organism, not organism and model, or having two layers of inheritance (but that is probably "over modelling" and getting more complex than you need).

This is only speculation thought. As ypercube requests in his comment: please give more detail such as sample data so that we can better understand what you are asking. Use the "edit" link to add the detail to your question rather than responding via comments.

Search and read around the concept of "table inheritance", you'll find examples in many places as it is a pretty common pattern. Many ORM based frameworks such as Hibernate (see http://hibernate.org/orm/what-is-an-orm/) use it for certain model types.

Related Question