0

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

1
  • 2
    You should show us the CREATE TABLE statements and even better sample data. The question is confusing as it is now. Commented Apr 20, 2016 at 10:20

2 Answers 2

1

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.

1
  • Schema which you typed is exactly what I want to do. I want to do table inheritance. I will share my data with you, when I collect it. Probably tomorrow or day after tomorrow. Thank you a lot. I also need to compare @Paparazzi answer to have wider view.
    – koralgooll
    Commented Apr 20, 2016 at 16:15
1

If I understand your question then I don't think it is going to work like you expect (want)

When you build up a query you can not use a variable (e.g. column value) as a table name for a join. This is not valid:

select *  
from mainTable mt
join mainTable.JoinTable as jt on jt.ID = mt.FK  

You will get an error mainTable.JoinTable not found (or maybe even not valid). Table names and column names are always evaluated literally.

You would need to use dynamic sql to build up the query

If you are worried about speed a single table with PK(organism, model) is going to be a very fast join. Even if you have a lot of empty columns in BigTable space is cheap. And varchar only uses I think 2 bytes for an empty value. And searching on the OneBig table will be very fast if you create an index on FK Organism, Model. If you are new to db you will be amazed at how well they handle a large volume of data.

If you have different columns another approach is a what I call a vertical table. This approach may NOT be best for your application - just putting it out there

Organism
--------  
OrdID PK    
Name   
Prop1   
Prop2  
...   

Model   
-----
OrgID PK FK  
NmID  PK   
Name     
Prop1   
Prop2   
...  

Name as separate column lets you change it and a little faster execution

PropDef  
-------  
OrgID  PK FK   
NmID   PK FK   
PropID PK  
Name   

PropVal   
-------
OrgID  PK FK  
NmID   PK FK  
PropID PK   
Value    

the selects are long
but if you have program building them up then that part is automated
and / or you can create views

select m.*, pv1.[value] as pv1value, pv2.[value] as pv2value
  from Model m  
  left join PropVal pv1
         on pv1.OrgID = m.OrgID 
        and pv1.NmID  = m.NmID 
        and pv1.PropID = 1 
  left join PropVal pv2
         on pv2.OrgID = m.OrgID 
        and pv2.NmID  = m.NmID 
        and pv2.PropID = 2 
 where m.OrgID = @OrgID and m.NmID = @NmID

That may look horrendous but building up multiple tables and unique selects for each table is not a minor task. With this you can let user (admins) add properties without add or modify any tables.

If you want to have multi-value properties then you add a table

PropValMV  
--------- 
OrgID  PK FK  
NmID   PK FK  
PropID PK   
Value  PK 
2
  • I want to do exactly what @David Spillett suggested in answer. What is your opinion about that?
    – koralgooll
    Commented Apr 20, 2016 at 16:17
  • 1
    @koralgooll Then go for it. That is a valid design. You did not really give enough detail.
    – paparazzo
    Commented Apr 20, 2016 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.