Normalisation help please

[Smiley Man]

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

 

[Smiley Man]

pfft typos dont mean anything

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

 

[Smiley Man]

Primary Key
Foreign Key

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

 

[Smiley Man]

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

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

 

[Smiley Man]

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

he just hates students

 

[Smiley Man]

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

 

[Smiley Man]

i want it to contain data on loads of vinyl and cd, i know by looking at the cd and vinyl table you cant tell what songs are on what cd or vinyl, but im pretty sure you'd use a query to do that

helium - i get ya now thanks

 

[Smiley Man]

You're missing the point, you cant ever have more than two fields in a table where the primary key has only two possible values. Yes or No.

Primary keys MUST be unique. You cant have two fields in there with the key as "Yes".

yeah that makes sense, the only way i can see of doing it now is to merge the cd and vinyl table into one though

i'm getting there, its just god damn keys i cant get my head around

 

[Smiley Man]

Vinyl Table:

Primary Key: Autonumber : Vinyl_ID
Name: Text
Date Released: Date

CD Table:

Primary Key: Autonumber : CD_ID
Name: Text
Date Released: Date

Remix Table:

Foreign keys:
CD_ID
Vinyl_ID

You do not need to merge anything, just change the data type of the CD? field to a number.

right ive just set it up like that and it still doesnt work when i try and save the remix table it tells me i need matching values in the CD and vinyl table (whichever one i put a value in), is this just access being a heap of crap? ive got referential integrity on in the relationship settings

 

[Smiley Man]

One solution - uninstall Access and never use it for normalisation again.

May not be appropriate, but seriously. Use SQL

sadly i cant use SQL - i havent got long left to do the assignment and i only know a tiny bit of SQL

dolph - no theres no data in the cd and vinyl tables, thats what i thought was wrong, im just trying to figure out a way round it

 

[Smiley Man]

i know what you're getting at but it just doesnt work, access refuses to update the vinyl and cd table from values put in the remix one

 

[Smiley Man]

ah ok

i can upload it for you to have a look at if you want, gimme a sec

here it is:
http://www.davidwatson.f2s.com/database.mdb

the damn keys are the only thing stopping me from really getting it all done

 

[Smiley Man]

*ding* ive got it now, seems my logic was out not access

if i have a tune on cd and not vinyl, is access gonna scream if i leave "vinyl ID" as blank (not 0)?