Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Sep 2006
    Posts
    66
    Thanks
    6
    Thanked 0 Times in 0 Posts

    When to use foreign keys?

    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
    Last edited by erdubya; 03-16-2009 at 07:35 PM. Reason: clarification

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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
    Last edited by Fumigator; 03-16-2009 at 07:52 PM.

  • Users who have thanked Fumigator for this post:

    erdubya (03-16-2009)

  • #3
    New Coder
    Join Date
    Sep 2006
    Posts
    66
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thanks for the timely response. Just what I needed.

  • #4
    New Coder
    Join Date
    Sep 2006
    Posts
    66
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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?

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • #6
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Maybe you could go even further with Fumi's idea... erm well, his earlier one at least.

    Code:
    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
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #7
    New Coder
    Join Date
    Sep 2006
    Posts
    66
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by bazz View Post
    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!

  • #8
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    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
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • Users who have thanked bazz for this post:

    erdubya (03-17-2009)


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •