Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have been studying the normal forms rules to guide me with database design and data storage, the problem I am having is that once I optimize for the first normal form (1NF), I figured I may well just go the whole way(even though not mandatory).

However a occurrence showed in my design that I haven't came across in examples online or books.

A example concept.

We want to store all data about the some car variations.

We must store values make, series, model, start year, end year.

Unormalized

+------+--------+-------+------------+----------+
| make | series | model | start year | end year |
+------+--------+-------+------------+----------+
| BMW  |   5    |  E12  |    1972    |   1981   |
| BMW  |   5    |  E28  |    1981    |   1988   |
| BMW  |   5    |  E34  |    1988    |   1996   |
| BMW  |   5    |  E39  |    1995    |   2004   |
+------+--------+-------+------------+----------+

Normalized

car_make

+----+------+
| id | make | 
+----+------+
| 1  | BMW  | 
+----+------+

car_series

+----+--------+---------+
| id | series | make_id |
+----+--------+---------+
| 1  |   1    |    1    |
| 2  |   3    |    1    |
| 3  |   5    |    1    |
| 4  |   7    |    1    |
+----+--------+---------+

car_model

+----+-------+------------+----------+-------- --+
| id | model | start year | end year | series_id |
+----+-------+------------+----------+-----------+
| 1  |  E12  |    1972    |   1981   |     3     |
| 2  |  E28  |    1981    |   1988   |     3     | 
| 3  |  E34  |    1988    |   1996   |     3     | 
| 4  |  E39  |    1995    |   2004   |     3     |  
+----+-------+------------+----------+-----------+

One problem arises here in the last table, should I also include a column make_id in the car_model table?

It is beneficial as I would not have to join car_series, then select make_id, to select make by id, however I believe this may not be sticking to normalization, because I would be repeating data(even though it is a id its still repeat).

Can this design be normalized further?

On another note with regards to the start year, end year handling, How would you professionals do this? I am curios if there is any range tricks to enforce integrity(although the E39 overlaps, lets speak as though no dates overlap).

Thanks in advance for any thoughts.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

No, you should not include a column make_id in the car_model table, it is implicitly defined by the series_id. If you need to see the make details you could make a view that looks like your un-normalized table.

No this design cannot be normalised further.

To force the year ranges to be non-overlapping you could add a trigger (as MySql does not support check constraints) to ensure that start_year >= max(end_year) and end_year >= start_year.

The trigger would look something like this:

create trigger trg_car_model_date_range_unique before insert on car_model
for each row
begin
    if new.start_year < (select max(end_year) from car_model) then
        signal sqlstate '45000' set message_text = 'Date range is not unique';
    end if;
    if new.end_year < new.start_year then
        signal sqlstate '45000' set message_text = 'Date range is invalid';
    end if;
end
share|improve this answer
    
How would you code the start_year >= max(end_year) constraint and what exactly would it solve? –  ypercube Feb 24 at 23:36
    
... Don't know why MySQL does not have check constraints... Anyway, answer updated with solution and clarification. –  Zack Feb 24 at 23:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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