...

View Full Version : When to use foreign keys?



erdubya
03-16-2009, 07:32 PM
I'm modeling my database project and wonder if it's a good idea to use foreign keys (joined tables) or not. There are over 6000 records in the table containing song information(title, album, artist, url). There will be several hundred users. Each user can rank an unlimited number of songs, as each song can be ranked by all users so users and songs have a many to many relationship.


Which option is best for data integrity and/or performance? Or is there a better option that will accomplish what i'm trying to do?


OPTION 1
=================
songrank table
--------------
user_id
user_name
song_id
rank

songlist table
--------------
song_id
song_title
song_artist
song_album
url



OPTION 2
=================
songrank table
--------------
user_id
user_name
song_id
song_title
song_artist
song_album
url
rank


Thanks,

erdubya

Fumigator
03-16-2009, 07:50 PM
If you go with the one table, you may as well just use a flat file because you won't be taking advantage of the "relational" aspect of a relational database.

Always normalize your data.

(p.s, I would add a third table to your option 1, and that is a cross reference table to relate users to songs, since a user will be able to rank many songs, and a song will have ranks from many users.)


user table
--------------
user_id
user_name

rank table
--------------
song_id
user_id
rank

songlist table
--------------
song_id
song_title
song_artist
song_album
url

erdubya
03-16-2009, 08:09 PM
Thanks for the timely response. Just what I needed.

erdubya
03-16-2009, 08:12 PM
One more question... If I wanted to do computations on rankings, for instance average rank, hi rank, low rank, average for the past 30 days, etc. Would it be better performance-wise to have those results written to a table or just perform the computation every time i execute a query for that info?

Fumigator
03-16-2009, 08:36 PM
Technically, of course, it will be faster/more efficient to store calculated results and use those results for your read-only processes. Of course this isn't the "purest" method; depending on how often you update your calculated results, users will be seeing outdated info. It really depends on your particular application as to what is the best way to go. If hundreds of users are pounding your database every minute, storing the calculations is probably a smart thing to do.

Best thing to do is run some timings on each method with a fully populated database and see if the extra overhead of the calculator queries is even substantial enough to consider storing calculations.

bazz
03-16-2009, 09:36 PM
Maybe you could go even further with Fumi's idea... erm well, his earlier one at least. :(



create table users
( user_name varchar (99) not null
, primary key (username)
) engine=innodb default charset=latin1;

create table songlist
( song_id int not null auto_increment
, title varchar (99) not null
, artist varchar (99) not null
, album varchar (99) not null
) engine=....

create table rank
( song_id int not null
, user_name varchar(99) not null
, rank varchar (4) not null
, CONSTRAINT rank_song_fk
foreign key (song_id)
references songlist (song_id)
, CONSTRAINT rank_username_fk
foreign key (user_name)
references users (user_name)
) engine.....


hth

bazz

erdubya
03-17-2009, 04:41 AM
create table rank
( song_id int not null
, user_name varchar(99) not null
, rank varchar (4) not null
, CONSTRAINT rank_song_fk
foreign key (song_id)
references songlist (song_id)
, CONSTRAINT rank_username_fk
foreign key (user_name)
references users (user_name)
) engine.....
[/code]

hth

bazz

Hi Bazz, I understand that a foreign key is a primary key from a different table. Outside of that basic definition of a foreign key, I'm confused as to what a constraint is or why its needed. and why it is used with a foreign key.

Thanks for helping a rookie!

bazz
03-17-2009, 06:06 AM
imagine you have a table with a foreign key in it. imagine that you delete the record in the parent table. where would the foreign key refer to then? the constraint tells mysql what you want to happen if you delete the parent record. there are (i think) 5 choices. default is restrict, which means that you can;t delete a parent record if there is a dependent child record in another table. you can also have
on delete cascade ~ deletes the child row if the parent is deleted
on update cascade ~ updates the child record if the parent is changed

look up FOREIGN KEY and CONSTRAINTS in your mysql docs.

bazz



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum