Normalisation help please

Smiley Man · 19 Apr 2007 at 09:04

yes i know its college work and i should do it but i think i know what the problem is i just need it confirming

im designing a database to store info on all my songs and here is the normalisation i have come up with:

which i have been told is correct, but when i come to set the database up in access with the relationships:

i get an error when i try and put an entry in the remix table like this:

i think this is because the vinyl and cd yes/no attributes are primary keys in their own tables and not in the remix table, but i cant see any way of getting round it

do i have to split the remix table into 2 seperate ones?

any advice much appreciated

calnen · 19 Apr 2007 at 09:06

I dont know anything about normalisation except in the maths/physics sense, but you seem to have spelt 'Artist' wrong in the first pic

SunaseIPs · 19 Apr 2007 at 09:08

calnen said:
I dont know anything about normalisation except in the maths/physics sense, but you seem to have spelt 'Artist' wrong in the first pic

Wouldn't affect the relationships of the database.

I see normalisation and switch off, I never understood what I was doing when I was doing it and when I was doing it I never knew if I was doing it right.

Smiley Man · 19 Apr 2007 at 09:09

pfft typos dont mean anything

yeah i know its so damn confusing, i thought i had it then access started getting in a flap :mad:

SunaseIPs · 19 Apr 2007 at 09:13

It would be like the blind leading the blind. Do you have the Connolly and Begg book? Database systems? ISBN 0-201-34287-1.

Covers normalisation well in there but I think its one of the fields in access you've defined. Check the properties of the fields in vinyl.

Chopper · 19 Apr 2007 at 09:26

I know the people that wrote that book, they were my lecturers at paisley university and one of them, thomas used to work for the company i work for now graham technology, im reading that book again this week while im off to refresh my mind on db's, might be able to help you in a few days, small world lol.

Helium_Junkie · 19 Apr 2007 at 09:34

Primary Key
Foreign Key

Code:

Artist	[U]ArtistID[/U]
	Artist

CD	[U]CD_ID[/U]
	CDWalletPage
	DateAdded

Vinyl	[U]Vinyl_ID[/U]
	DateAdded

Song	[U]SongID[/U]
	[I]ArtistID[/I]
	SongName

Remix	[U]RemixID[/U]
	[I]SongID[/I]
	Remix
	Duration
	Key
	[I]CD_ID[/I]
	[I]Vinyl_ID[/I]

Your relationships look correct, but I'm worried about what the ? is for in your "CD?" "Vinyl?" fields. No CD or Vinyl would be signified by not having an entry for the foreign key, i.e. CD_ID = NULL, not by a "Yes/No" field. Therefore by using CD_ID you don't (I think) need "CD No" as they would be the same thing?

Incidentally, you appear to have lost Genre somewhere along the line.

Smiley Man · 19 Apr 2007 at 09:54

Helium_Junkie said:
Primary Key
Foreign Key

Code:

Artist [U]ArtistID[/U] Artist CD [U]CD_ID[/U] CDWalletPage DateAdded Vinyl [U]Vinyl_ID[/U] DateAdded Song [U]SongID[/U] [I]ArtistID[/I] SongName Remix [U]RemixID[/U] [I]SongID[/I] Remix Duration Key [I]CD_ID[/I] [I]Vinyl_ID[/I]

Your relationships look correct, but I'm worried about what the ? is for in your "CD?" "Vinyl?" fields. No CD or Vinyl would be signified by not having an entry for the foreign key, i.e. CD_ID = NULL, not by a "Yes/No" field. Therefore by using CD_ID you don't (I think) need "CD No" as they would be the same thing?

Incidentally, you appear to have lost Genre somewhere along the line.

good point about genre - thanks

CD? and Vinyl? are yes/no boxes

some tunes can be CD or Vinyl, or both, so there is always going to be a value (yes or no, ticked or not ticked) but i honestly dont know whats going on, access doesnt seem to want to update the cd and vinyl table from the remix one

thanks for all the help so far guys

Helium_Junkie · 19 Apr 2007 at 10:03

Smiley Man said:
some tunes can be CD or Vinyl, or both, so there is always going to be a value (yes or no, ticked or not ticked)

This is not the way it should be done. Yes/No boxes which then link to another table makes my head hurt. Change the Yes/No to a foreign key. Then if your remix is on a CD you put a key in. If it isnt, you dont. Same general purpose as the tickbox, but with the link to the CD/Vinyl all rolled into one.

Otherwise how would your CD table link to the remix? In what you've put down there is no shared key in those tables other than a tickbox - which means your primary key in a table has two possible values, and therefore you can only ever have 2 rows in that table! It makes me think that perhaps you're picturing having lots of seperate CD/Vinyl tables, one for each remix... which is not a normalised approach.

helmet · 19 Apr 2007 at 10:05

You are trying drag a null value across from the CD or Vinyl field from its respective tbl but you cannot as the record does not exist, its got to be Y or N.

Or to put it another way....

you are pulling 2 values across into Remix when you have only entered 1 value.

Change the table proprties of the fields:
CD_ID
Vinyl_ID

to not required.

helmet · 19 Apr 2007 at 10:07

P.S. get your teacher to check it again, I think you may need other fields and that ^^ is not normalized correctly.

singist · 19 Apr 2007 at 10:12

Smiley Man said:
pfft typos dont mean anything

yeah i know its so damn confusing, i thought i had it then access started getting in a flap

It is NOT a typo; it is a nineteen years old DJ who doesn't know the difference between an artist and an artiste trying to pass off their ignorance as a typo.

Smiley Man · 19 Apr 2007 at 10:15

singist said:
It is NOT a typo; it is a nineteen years old DJ who doesn't know the difference between an artist and an artiste trying to pass off their ignorance as a typo.

its a typo god damn it :mad:

so if the box isnt ticked its a null value? right that makes sense, will give it a go now

Helium_Junkie · 19 Apr 2007 at 10:15

singist said:
It is NOT a typo; it is a nineteen years old DJ who doesn't know the difference between an artist and an artiste trying to pass off their ignorance as a typo.

What?
The typo was "Atrist", not "Artiste" ... what are you talking about?

Smiley Man · 19 Apr 2007 at 10:16

Helium_Junkie said:
What?
The typo was "Atrist", not "Artiste" ... what are you talking about?

he just hates students

Helium_Junkie · 19 Apr 2007 at 10:17

Smiley Man said:
its a typo god damn it

so if the box isnt ticked its a null value? right that makes sense, will give it a go now

I'm telling you, having a yes/no as a key is wrong. Stop and do it with a proper alphanumeric ID!

Your error message at the top is probably because you've tried to link to a vinyl but you havent first created the vinyl in the table. You can stop that error by adding two fields to Vinyl, one ticked, one not.

Do you really intend your database to only ever hold data of one vinyl and one CD?

Smiley Man · 19 Apr 2007 at 10:22

Helium_Junkie said:
I'm telling you, having a yes/no as a key is wrong. Stop and do it with a proper alphanumeric ID!

so something like:

1 - cd
2 - vinyl
3 - both

?

thanks

helmet · 19 Apr 2007 at 10:24

Helium_Junkie said:
I'm telling you, having a yes/no as a key is wrong. Stop and do it with a proper alphanumeric ID!

^^ Also, sit down and think about what you are storing and why. That will give you the field names needed. Then, forget about logical thinking and do what the 3 rules say, take each field one at a time and then apply the rules....

helmet · 19 Apr 2007 at 10:26

http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html

Helium_Junkie · 19 Apr 2007 at 10:29

Smiley Man said:
so something like:

1 - cd
2 - vinyl
3 - both

?

thanks

Stop thinking of it from the remix table point of view, and look at the Vinyl table.
How many vinyls do you want your database to hold, one? or Many.
If one, then sure a tickbox is fine.
But if you want more than one vinyl in your database you need to delete that evil tickbox and change the ID of the Vinyl to a number, just like the artist table, the remix table, the song table...

Current possible fields in Vinyl table:

Yes - Number - Date
No - Number - Date
-------- thats it, your primary keys are used up, you can never again have a vinyl.