Is This Data Normalised?

Soldato
Joined
10 May 2004
Posts
3,732
Location
East Yorkshire, UK
Hi,

Just wondering is this data normalised?

Table: Member
Member Number
First Name
Last Name
House Name
Street 1
Street 2
City
State/Province
Country/Region
Postal Code
Email Address
Home Phone
Birth Date

Table: Payment
Payment Id
Pay 08
Pay 07
Pay 06
Pay 05


Table: Dinner
Dinner Id
Attendance
Choice
Paid


Or can this be normalised further?
 

daz

daz

Soldato
Joined
18 Oct 2002
Posts
24,048
Location
Bucks
It looks normalised (you have no repeated data, each entity has it's own table etc) but you don't seem to have any links between the tables?
 
Soldato
Joined
12 Feb 2006
Posts
14,282
Location
Surrey
well you'd want more fields in the tables and then link them, so say with member number, you'd also put a field with that in the other 2 tables and then link them so when someone places an order then it stores the member number so you know who did it.
 
Soldato
Joined
10 May 2004
Posts
3,732
Location
East Yorkshire, UK
so...

Table: Member

Member Number
First Name
Last Name
House Name
Street 1
Street 2
City
State/Province
Country/Region
Postal Code
Email Address
Home Phone
Birth Date

Table: Payment
Member Number
Payment Id
Pay 08
Pay 07
Pay 06
Pay 05


Table: Dinner
Member Number
Dinner Id
Attendance
Choice
Paid

that what you mean?
 
Soldato
Joined
12 Apr 2004
Posts
11,788
Location
Somewhere
What type of links would I need?

Depends on how your application works. The Dinner table name seems to be a bit misleading, as that table seems to be storing information about attendances rather than actual dinners (it has fields that specify whether it's been paid for and which dinner it is).

Assuming this table does store attendances, you'd want to rename it to reflect that, then create another table for storing actual dinner types. The Choice field in the attendances table would then be a foreign key linking to the (new) Dinner table.

I'm not sure what the Attendance field is supposed to store though :)
 
Soldato
Joined
10 May 2004
Posts
3,732
Location
East Yorkshire, UK
The attendance field is for whether the member is going to the dinner, the table is for whether they are going to the dinner, and if they are what menu are they having (Chicken, Salmon or Pasta) and if they have paid for the dinner yet.
 
Soldato
Joined
12 Apr 2004
Posts
11,788
Location
Somewhere
In that case I'd do this:

Code:
[B]Attendances[/B]
Attendance ID
[I]Member Number[/I]
Dinner
Paid

Italics denote a foreign key.

You don't need an attendance field if you only store records in that table for members who are attending anyway :)

Technically, in order for it to be properly normalised, you should really take it a step further and factor out dinners into a separate table, so as to avoid repetition of dinner information in the Attendance table:

Code:
[B]Attendances[/B]
Attendance ID
[I]Member Number[/I]
[I]Dinner ID[/I]
Paid

[B]Dinner[/B]
Dinner ID
Name
(first course, second course, etc. as well if you want to)
 
Last edited:
Soldato
Joined
12 Apr 2004
Posts
11,788
Location
Somewhere
For a one-to-many or many-to-one relationship, yep. If it's a many-to-many relationship you have to create a separate table just to store the links.

For example, you're storing information about songs and albums in a database. An album obviously contains many songs, but a song can also belong to multiple albums (singles, EPs, compilations, etc.). Because we have a many-to-many relationship, we create an intermediate table:

Album
Album ID
Name
Publisher

Song
Song ID
Name
Length

Song_Album
Link ID
Song ID
Album ID

This way, in order to store the fact that song 4 belongs to albums 8 and 9 and song 5 also belongs to album 9, we would add the following records to Song_Album:

{Song ID = 4, Album ID = 8}
{Song ID = 4, Album ID = 9}
{Song ID = 5, Album ID = 9}
 
Last edited:
Soldato
Joined
18 Oct 2002
Posts
3,926
Location
SW London
Hi,

Just wondering is this data normalised?

Table: Member
Member Number
First Name
Last Name
House Name
Street 1
Street 2
City
State/Province
Country/Region
Postal Code
Email Address
Home Phone
Birth Date

Table: Payment
Payment Id
Pay 08
Pay 07
Pay 06
Pay 05


Table: Dinner
Dinner Id
Attendance
Choice
Paid


Or can this be normalised further?

If you're going to be really strict you could have lookup tables for City, State/Province and Country/Region.

Indeed you could separate the addresses out entirely into a new table.
That way multiple members could belong to the same address without duplication (i.e. a family living together)

Your payment table has what appear to be individual years. This is not normalised, you should have a date field or something rather than specifying years in the columns.
 
Top Bottom