Is This Data Normalised?

decmatt · 5 May 2008 at 11:03

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 · 5 May 2008 at 11:04

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?

decmatt · 5 May 2008 at 11:05

What type of links would I need?

unwashed potato! · 5 May 2008 at 11:11

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.

decmatt · 5 May 2008 at 11:19

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?

Inquisitor · 5 May 2008 at 11:20

Solac said:
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

decmatt · 5 May 2008 at 11:26

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.

Inquisitor · 5 May 2008 at 11:29

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)

decmatt · 5 May 2008 at 11:34

Many thanks

So when a relationship is made do i do it Member ID <=> Member ID?

Inquisitor · 5 May 2008 at 11:40

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}

decmatt · 5 May 2008 at 11:55

Many thanks, get it now

Haircut · 5 May 2008 at 12:25

Solac said:
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.