How does this normalized example work?

Question

I had the following DB schema:

Customer   Car     Rental
-------   ----     ------
Name      Name     Car_ID
ID        ID       Customer_ID
                   Date

This is said to be non normalized as the date can repeat (multiple customers can rent a car at the same day). So the teacher said it should be like:

Customer  Car Date
------    --- ----

and linked with foreign keys.

Well I do not get that - how can I then simply enter that "John rented a BMW on 2/3/2013"?

You would still have the Rental table only instead of holding a date it would have a link to that date in the date table. Date: (id:111, date:2/3/2013) Rental: (car_id: 123, customer_id: 456, date_id: 111) — thorsten müller
– thorsten müller, Commented Nov 15, 2013 at 13:02
The point is - they said so - there is no need for rental table now. — John V
– John V, Commented Nov 15, 2013 at 13:05
You do need a Rentals table to link customers and cars together on any given date, otherwise a car can be rented only once (Car.CustomerID) or a customer can once rent one car (Customer.CarID), which is just silly. — CodeCaster
– CodeCaster, Commented Nov 15, 2013 at 13:08
...or you can only have one transaction per day. Also silly. — Kilian Foth
– Kilian Foth, Commented Nov 15, 2013 at 13:13
Yes it could, but then it would just be a badly-named Rentals table. — CodeCaster
– CodeCaster, Commented Nov 15, 2013 at 13:26

Kilian Foth · Accepted Answer · 2013-11-15 13:04:34Z

4

You would enter that fact by first creating (or looking up) a record in Date to represent Mar 2, 2013, then create a new record in Rental with three foreign keys. (You still need the table Rental, otherwise you can't represent arbitrary re-rentings of a customer or a car - or on the same date.)

However, that is surely unnecessary. A date is one scalar piece of information - the fact that it's formatted XX/YY/ZZZZ or MMM DD, YYYY doesn't affect its identity, and formatting rules shouldn't be imposed by the storing the display form in databases anyway. My opinion is that storing dates as a scalar field in another table (here: Rental) is perfectly normal - a date is effectively a day number, i.e. no worse than any other foreign key you'd use.

answered Nov 15, 2013 at 13:04

Kilian Foth

111k45 gold badges302 silver badges323 bronze badges

In this example, I agree about the date, but there could be other information associated with a date like, billing period, IsHoliday. and might even need to be linked to a fee schedule or discount table.

JeffO
– JeffO

2013-11-15 19:29:58 +00:00
Commented Nov 15, 2013 at 19:29

Add a comment |

Stack Exchange Network

How does this normalized example work?

1 Answer 1

Your Answer

Hot Network Questions

How does this normalized example work?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions