[Libertine]

Firstly, apologies for bringing this horrible database problem. This is my attempt at an entity relationship diagram, I realise that it does not show the relationships - I have a poor understanding of normalisation. I have produced this in preparation for making a database based on a case study of a holiday cottage company. To make things simple, lets say there's an arbitrary number of cottages, 4 will do for now.

My questions:

• What normalised form, if any, can this ERD be considered to be in? (1st, 2nd or 3rd?)
  
• The 'cottage ID' field in the 'Bookings' table will contain repeated values - it will always be 1,2,3 or 4. Is this a problem?
  
• If this is a problem, I could resolve this by creating a link entity right? What are the advantages of this? I'm thinking it's unnecessary - persuade me if it's appropriate.

Many thanks.

[woodjom]

as this diagram suggests, what is the Bookings control field? Since Cottage ID and Customer ID are Foreign Key Fields, what is keeping the Bookings data point unique from the other data?

When i do a child table with several parent tables describing the fields within the child table, its best to have a unique ID that will describe that specific booking. Some people use a combination of parent control fields and other use a unique control field. In either point, no single data point within the table will have the same value regardless of the description fields (Reference, Status, Start Date, End Date, etc).

But outside of what i have responded there is not alot we can do to help you, as the overall picture of this ERD is not very descriptive.

[baavgai]

Any marginally normalized database will be somewhere around the second and third form. It's rare to see completely third form databases in the wild; most strike a balance between integrity and maintainability. Also, the primeness of attributes can be subjective.

Let's say Customers and Cottages are fine, and look at Bookings. Bookings has no obvious primary key. This is because date ranges are ugly and allow overlap. Also, I don't know what "status" is, but expect it wants a table. I don't know what reference is.

Bookings primary key would be at least CustomerId and CottageId, but it needs more. StartDate is good. Again, date ranges are a pain. A cleaner way might be to have another table like Reservation(BookingsId,CottageId,Day). In this way, you can't book at cottage twice.

[woodjom]

In my experience, i do not use normalization to design a database but a couple rules.

Rules:
1) Any information that could be flexibly increased or decreased needs to be in its own table.
2) When designing a table, make sure that the descriptive fields of that table will define only that data point and nothing else. If information that will be associated with that datapoint will describe another tables data point, its probably best to create a consolidated table of information and reference it in the tables that need it.
3) If you overthink it, then it will be way overly complex for anyone else to make sense of it. Not all descriptive fields will need to be in their own table.
4) KISS (Keep It Simple Stupid).

Simple Rules for a Simple Person....man i should be a Hobbit.

This post has been edited by woodjom: 17 February 2010 - 01:32 PM