Normalisation Question

Database normalisation problem

Page 1 of 1

3 Replies - 1987 Views - Last Post: 17 February 2010 - 01:18 PM Rate Topic: -----

#1 Libertine  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 01-November 09

Normalisation Question

Posted 16 February 2010 - 11:01 AM

Posted Image

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Normalisation Question

#2 woodjom  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 29
  • View blog
  • Posts: 549
  • Joined: 08-May 08

Re: Normalisation Question

Posted 17 February 2010 - 12:26 PM

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.
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5795
  • View blog
  • Posts: 12,627
  • Joined: 16-October 07

Re: Normalisation Question

Posted 17 February 2010 - 12:51 PM

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.
Was This Post Helpful? 0
  • +
  • -

#4 woodjom  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 29
  • View blog
  • Posts: 549
  • Joined: 08-May 08

Re: Normalisation Question

Posted 17 February 2010 - 01:18 PM

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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1