Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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"?

share|improve this question
    
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 Nov 15 '13 at 13:02
    
The point is - they said so - there is no need for rental table now. –  user970696 Nov 15 '13 at 13:05
1  
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 Nov 15 '13 at 13:08
    
...or you can only have one transaction per day. Also silly. –  Kilian Foth Nov 15 '13 at 13:13
1  
Yes it could, but then it would just be a badly-named Rentals table. –  CodeCaster Nov 15 '13 at 13:26

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
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 Nov 15 '13 at 19:29

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.