CODEWITHSUNDEEP
database-designdatabase-normalization
Alina
Alina

Reputation: 142

Database modeling and database normalization

I am modeling a normalized database for a heat network (it should be in 3NF). In this table the structure is like this (I removed unimportant data):

Here I can see only one way wrong data can be added... If in invoice_details we link to the consumption history of another heat unit. I haven't found a way, other than by programming this constraint, to make sure that in invoice_details I will have only consumption_history_id's for the heat_unit of the current client.

Is there a way I can add this constraint in the database? Or is it ok if I add it in the specifications and implement it programatically?

Edit: This is the database before adding surrogate keys

Heat_units (id (PK), name);

Clients (client_code (PK), client_name, heat_unit_id (FK));

Heat_unit_consumption_history ( date (PK), heat_unit_id (PK, FK), used_energy) - where (date, heat_unit_id) - represent a composite primary key;

Invoices (invoice_number (PK), start_date, end_date, client_code (FK));

Invoice_details (invoice_number (PK, FK), heat_unit_consumption_date (PK, FK), heat_unit_id (FK), price_history_start_date (FK)) - invoice_number and heat_unit_consumption_date are a composite primary key;

Prices_history (start_date (PK), price)

Upvotes: 1

Views: 83

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56755

I have found in cases like this that the use of surrogate keys (the ID columns) is confusing the ability to see the desired inter-table constraints when there are multiple or transitive dependencies.

Firstly when you use a surrogate key, you should not treat that as an excuse to not have a natural key. As much as possible (+90% of the time) the table's natural key should be preserved in the design as an Alternate Key (AK). An alternate key is just an additional Unique Key(UK) constraint on the table. Functionally this UK is also an alias for the PK, and thus in data-design we call them Alternate Keys (AK), though in physical implementation they are Unique Keys (UK).

Thus a table like this:

Clients(id (PK), client_name, heat_unit_id (FK))

Is unacceptable because clients obviously can be naturally identified by their name also. Thus this is much more likely to be the correct design for the Clients table:

Clients(id (PK), client_name (AK), heat_unit_id (FK))

Now, why this is important, is because the best way to data-design is to treat the surrogate keys as physical implementation artifacts (for almost all cases) that you only add at the end of the logical design. (The only exceptions to this are things that there is no reasonable way to define a natural key, like say the lines of a cash register receipt or the physical lines of a text file.)

If you look at this answer: Enforce composite unique constraint that depends on parent column value I walk through how to do this for complex cases with transitive relational constraints like yours. Unfortunately, I am do not have enough time today to walk through the whole process for your example, but hopefully you can figure it out from this (or perhaps some other answerer).

In any event, the overall technique is

  1. take off the surrogate keys,
  2. assign natural primary/alternate keys
  3. do the data design, using natural foreign keys
  4. convert these back into surrogate keys (IDs)

Upvotes: 1

Related Questions