Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2010
    Posts
    3

    Unanswered: Designing Project

    Hello guys, I'm new here and I'm working on a music database project, it needs to be very flexible as in I can see how many instruments a artist played, in what song an artist played a certain instrument etc etc.

    I'm just really wondering about a certain thing; When I have 2 tables that I want to connect together, for example Album and Songs, would I then put one more table between named, Album_Songs, and in it I would have a primary key, for example Album_Songs_id , and then Songs_id foreign key referenced to the primary key in the Songs table.

    Could I then insert multiple Album_Songs_id values? So that I can see what songs are in each album?

    Sorry if I didn't explain well enough, thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, you can certainly have an albums table, a songs table, and an album_songs table to link to the two. The album_songs table will allow you to see which album(s) a song is on, and which songs are on an album or group of albums.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2010
    Posts
    3

    Thanks

    Ok thanks, I'm reading about composite key's, would you recommend using them?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Very difficult to have a relational database without any.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Given two tables Album and Song, you have two ways to design the junction table Album_Song.
    Code:
    CREATE TABLE #Album(
    	AlbumId		INT	Identity(1, 1)	NOT NULL,
    	.....,
    	CONSTRAINT PK_Album PRIMARY KEY (AlbumId)
    )
    
    CREATE TABLE #Song(
    	SongId		INT	Identity(1, 1)	NOT NULL,
    	.....,
    	CONSTRAINT PK_Song PRIMARY KEY (SongId)
    )
    
    CREATE TABLE #Album_Song_A(
    	AlbumId		INT		NOT NULL,
    	SongId		INT		NOT NULL
    	CONSTRAINT PK_Album_Song_A PRIMARY KEY (AlbumId, SongId)
    )
    
    CREATE TABLE #Album_Song_B(
    	Album_SongId	INT	Identity(1, 1)	NOT NULL,
    	AlbumId		INT		NOT NULL,
    	SongId		INT		NOT NULL
    	CONSTRAINT PK_Album_Song_B PRIMARY KEY (Album_SongId)
    )
    In the A-version, you create a composite primary key, consisting of the foreign keys to Album and Song.
    Another table X referencing a certain Album_Song record, will include AlbumId and SongId in its table definition. Still another table Y referencing a certain table X record, will also include AlbumId and SongId in its table definition. And so on.
    A large part of a table definition can end up being taken in by foreign keys.
    To get results from a certain Album and Table Y, you can immediately join both tables on the AlbumId.

    In the B-version, you create a table with a technical PK, and two foreign keys to Album and Song.
    Another table X referencing a certain Album_Song record, will not include any AlbumId or SongId columns in its table definition, but an Album_SongId column.
    A foreign key referencing another table will always occupy 1 column.
    To get results from a certain Album and Table Y, you must follow (join) all the junction tables between Album and TableY: Album to Album_Song, Album_Song to Table X, Table X to table Y.

    The biggest difference between both approaches surfaces when you have to change a primary key in Album or Song. Suppose you find out that a number of Songs and/or Albums occur more than once in the database.
    Suppose The Album with AlbumId = 1 occurs 3 times in the database: with AlbumId 1, 101 and 1001. You want to retain only AlbumId = 1 and delete the others, after redirecting all the foreign keys to AlbumId = 1.

    In the A-approach you have the values AlbumId 1, 101 and 1001 spread all over your database in multiple tables. Normally all secured through Foreign Key constraints. You will have to recursively identify all the tables that have a foreign key to Album, then all the tables that reference those tables through an AlbumId, then .... Then you will have to UPDATE all those tables one by one, but doing it in such a sequence that you never violate a foreign key constraint. Or you could take the short cut and drop all the foreign key constraints, do the UPDATEs and then reinforce the foreign keys again.

    Compare this to:

    In the B-approach you first
    Code:
    UPDATE Album_Song
    SET AlbumId = 1
    WHERE AlbumId IN (101, 1001)
    followed by a
    Code:
    DELETE
    FROM Album
    WHERE AlbumId IN (101, 1001)
    Done.

    I switched to the B-approach after having had to clean up a database with countless doubles. I know, I know, things like that don't happen in a perfect world, but I don't live in one.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Wim - the technical or surrogate key does not mean there should be no natural key enforced. The difference between A and B should be the addition of the technical key only, not the removal of the natural key.

    Quote Originally Posted by Wim View Post
    I switched to the B-approach after having had to clean up a database with countless doubles. I know, I know, things like that don't happen in a perfect world, but I don't live in one.
    I'd be interested in seeing the DML to create a "double" in A. The only "double" I can see that could be produced would be in B.

    Technical keys serve one single purpose in an association table - to act as a proxy, or surrogate, for the natural key throughout the rest of the database.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by pootle flump View Post
    Technical keys serve one single purpose in an association table - to act as a proxy, or surrogate, for the natural key throughout the rest of the database.
    I'll certainly agree that technical keys SHOULD only serve as a proxy for the natural key.

    I've seen technical (surrogate) keys used for all kinds of other things, most of which give me a bad case of the willies!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Oct 2010
    Posts
    3
    http://img24.imageshack.us/img24/979...esigngsf30.jpg

    Here's the complete design, what do you guys think?

    It's a bit of a clusterfuck so I apologize for that.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan View Post
    I'll certainly agree that technical keys SHOULD only serve as a proxy for the natural key.

    I've seen technical (surrogate) keys used for all kinds of other things, most of which give me a bad case of the willies!
    Yeah, I didn't want to get in to some of the more obscure uses of technical keys. To put it another way, a technical key never requires (nor is it desirable) the removal of other keys.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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