CODEWITHSUNDEEP
mysqlsql
urschrei
urschrei

Reputation: 26859

How to structure these relations?

I have a table tablet, with a column period_id. The tablet table has a many-to-one relationship with a period table, and the period table has a one-to-many relationship with a sub_period table. I need to be able to specify a single sub_period entry in or from tablet, the choice of which is restricted by the period entry, but since sub_period only depends on period, I can't. How should I model this?

In my model, each tablet row should have one period entry, and one sub_period entry. The relationship between period and sub_period should be 1:n (a period may have many sub-periods, but each sub-period can only belong to a single period). The sub-periods I'd like to be able to choose from in tablet should be restricted by my choice of period

Edit: Have realised that I need an N:1 relationship between tablet and period: many tablets can relate to the same period.

Upvotes: 0

Views: 68

Answers (3)

Quassnoi
Quassnoi

Reputation: 425251

Create a nullable column has_tablet ENUM('1') in sub_period and create a UNIQUE constraint on sub_period (period, has_tablet).

has_tablet can only accept values 1 or NULL which means you can set exactly one season per period as "having the tablet". Since period to tablet is one-to-one, this uniquely identifies the tablet too.

It is possible not to set any tablet for a period at all with this solution. If it's a problem, add period to tablet and make tablet (period, sub_period) reference sub_period (period, sub_period).

This is a denormalization but it can never lead to an inconsistency (since the period is defined by sub_period in both tables).

Update:

Seems that period to tablet is one-to-many. In this case, just add make tablet reference sub_period rather than period. period relationship can be transitively inferred by a simple join.

Upvotes: 2

jcho360
jcho360

Reputation: 3759

When you are modeling you, normally you don't create 1:1 relations, when you normalize you'll get notice that the table with relation 1:1 you can match them into a table, this is how I see it: if you have 3 tables with relations

  • tablet 1:n sub_period
  • tablet 1:1 period

(the keys in the graphic are the primaries keys and the red symbol is the foreign key)

enter image description here

or you can make a match between the Tablet and Period table and the model will look like this

enter image description here

where Tablet absorb Period with all his attributes, but that's up to you, the model you have to adapt it to your needs.

Upvotes: 0

Sebas
Sebas

Reputation: 21522

Then you have 2 options:

  • database layer: implement consistency contraints through triggers. after insert or update, if sub_pediod is not a sub_period of the tablet's period, raise the appropriate error.
  • applicative layer: implement the proper GUI to provide the right choices only, whenever it is necessary.

The second option is my favorite whenever it is about non sensitive data (keep in mind somebody could always skip the client interface), because it usually gives me more "range of motion" with how I want to deal with the problem. Of course there are always exceptions...

Upvotes: 0

Related Questions