Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Vancouver, BC
    Posts
    35

    IsA relationships

    Attached is an ERD of a music playlist schema. The problem I am having is dealing with the Track entity. I was thinking of the following schema for Track:

    Code:
    CREATE TABLE Tracks (
        PlayListId INT,
        ConcerTrackId INT,
        SessionTrackId INT,
        StoryTrackId INT,
        BandTrackId INT,
        FOREIGN KEY (ConcertTrackId) REFERENCES ConcertTracks,
        FOREIGN KEY (SessionTrackId) REFERENCES SessionTracks,
        FOREIGN KEY (StoryTrackId) REFERENCES StoryTracks,
        FOREIGN KEY (BandTrackId) REFERENCES BandTracks,
        FOREIGN KEY (PlayListId) REFERENCES Playlists
    )
    I could write up some triggers that would ensure that a track would only be related to one of those *Tracks entities. To me this looks a bit messy but I can't think of another way.
    Attached Thumbnails Attached Thumbnails Diagram1.png  
    INSERT INTO Jokes (statement) VALUES ('Here is my witty SQL signature');

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Do all of those different tracks have different attributes? I mean, is the table structure in StoryTracks different from the table structure in BandTracks? If not, you could store them in a single "Tracks" table and have another table for Track Type.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I think you've got a couple of problems. At least for me, a track can appear in many playlists, and any given track can also have more than one "class" such as bandtrack and concertrack. Sounds to me like you need to cook up a couple of many-to-many relationships to make this work as I understand it.

    -PatP

  4. #4
    Join Date
    Dec 2002
    Location
    Vancouver, BC
    Posts
    35
    Teddy, I thought about that, but for this application each type of track has many unique attributes.

    Pat, yes a track is allowed to be in many playlists. If more than 1 user wants to have a song in their playlist that's fine. To prevent a track from havinging multiple "classes" I figured that I could use a trigger to maintain that constraint.
    INSERT INTO Jokes (statement) VALUES ('Here is my witty SQL signature');

  5. #5
    Join Date
    Jan 2006
    Posts
    18
    disclaimer: i've been out of database design for a while, and may be hopelessly clueless.

    i don't like this - a track ceases to exist unless it's in a playlist or category?

    if a given track can only have one categorization, you want a one-to-many relationship. i'd create a table of categories and have each track reference it:

    Code:
    table tracks:
    - track_id (primary key)
    - category_id (references categories.category_id)
    - track_name
    - etc.
    
    table categories:
    - category_id (primary key)
    - category_name
    then have separate tables containing the information for each category type that you query individually based on the returned category_id for the track:

    Code:
    table concert_tracks:
    - track_id (foreign AND primary key)
    - composer
    - orchestra
    - conductor
    - etc.
    
    table session_tracks:
    - track_id (foreign AND primary key)
    - studio
    - date
    - etc.
    most everything else is a many-to-many relationship. playlists must be an intermediate table:

    Code:
    table user_playlists
    - user_id
    - playlist_id
    - playlist_track_number
    - track_id
    (primary key user_id and playlist_id and playlist_track_number)
    that allows a user to have many playlists, each playlist containing many tracks with a specified order, each track belonging to a single category, with category-specific information specified.

  6. #6
    Join Date
    Dec 2002
    Location
    Vancouver, BC
    Posts
    35
    Yeah, I ended up doing it in a similar way. Here's the schema:

    Code:
    CREATE TABLE [dbo].[Music_TrackTypes] (
    	[TrackTypeId]		INT IDENTITY (1, 1),
    	[TypeName]			NVARCHAR(32),
    	PRIMARY KEY NONCLUSTERED ([TrackTypeId])
    )
    GO
    
    CREATE TABLE [dbo].[Music_Tracks] (
    	[TrackId]	INT IDENTITY (1, 1),
    	[AudioFile]			NVARCHAR(128),
    	[TrackTypeId]		INT,
    	[Length]			INT,
    	[Name]				NVARCHAR(128),
    	[NameUrl]			NVARCHAR(128),
    	[Description]		NVARCHAR(128),
    	[Credits]			NVARCHAR(128),
    	[Writer]			NVARCHAR(256),
    	[Publisher]			NVARCHAR(256),
    	[Copyright]			NVARCHAR(256),
    	PRIMARY KEY NONCLUSTERED ([TrackId]),
    	FOREIGN KEY ([TrackTypeId]) REFERENCES [dbo].[Music_TrackTypes]
    )
    GO
    CREATE INDEX [IX_Music_Tracks_NameUrl] ON [dbo].[Music_Tracks](NameUrl)
    GO
    
    -----------------------------------------------------
    -- Playlists
    -----------------------------------------------------
    
    --CREATE TABLE [dbo].[Music_PlaylistTypes] (
    --	[PlaylistTypeId]		INT IDENTITY (1, 1),
    --	[TypeName]				NVARCHAR(32),
    --	PRIMARY KEY NONCLUSTERED ([PlaylistTypeId])
    --)
    --GO
    	
    
    CREATE TABLE [dbo].[Music_Playlists] (
    	[PlaylistId]		INT IDENTITY (1, 1),
    	[Name]				NVARCHAR(128),
    	[NameUrl]			NVARCHAR(128),
    	[PlaylistTypeId]	INT,
    	PRIMARY KEY NONCLUSTERED ([PlaylistId]),
    	--FOREIGN KEY ([PlaylistTypeId]) REFERENCES [dbo].[Music_PlaylistTypes]	
    )
    GO
    CREATE INDEX [IX_Music_Playlists_NameUrl] ON [dbo].[Music_Playlists]([NameUrl])
    GO
    
    -----------------------------------------------------
    -- Playlist tracks
    -----------------------------------------------------
    
    CREATE TABLE [dbo].[Music_PlaylistTracks] (
    	[PlaylistId]		INT,
    	[Sequence]			INT,
    	[TrackId]			INT,
    	PRIMARY KEY ([PlaylistId], [Sequence]),
    	FOREIGN KEY ([PlaylistId]) REFERENCES [dbo].[Music_Playlists] ON DELETE CASCADE,
    	FOREIGN KEY ([TrackId]) REFERENCES [dbo].[Music_Tracks] ON DELETE CASCADE
    )
    GO
    
    -----------------------------------------------------
    -- User Playlists Collection and Favourites
    -----------------------------------------------------
    
    CREATE TABLE [dbo].[Music_UserPlaylists] (
    	[UserId]			INT NOT NULL,
    	[PlaylistId]		INT, 
    	PRIMARY KEY ([UserId], [PlaylistId]),
    	FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users] ON DELETE CASCADE,
    	FOREIGN KEY ([PlaylistId]) REFERENCES [dbo].[Music_Playlists] ON DELETE CASCADE
    )
    GO
    
    CREATE TABLE [dbo].[Music_FavouritePlaylists] (
    	[UserPlaylistId]	INT IDENTITY (1, 1),
    	[UserId]			INT NOT NULL,
    	[PlaylistId]		INT, 
    	[TrackId]			INT,
    	PRIMARY KEY ([UserPlaylistId]),
    	FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users] ON DELETE CASCADE,
    	FOREIGN KEY ([PlaylistId]) REFERENCES [dbo].[Music_Playlists] ON DELETE CASCADE,
    	FOREIGN KEY ([TrackId]) REFERENCES [dbo].[Music_Tracks] ON DELETE CASCADE
    )
    GO
    
    CREATE TABLE [dbo].[Music_StaffPlaylists] (
    	[PlaylistId]	INT,
    	FOREIGN KEY ([PlaylistId]) REFERENCES [dbo].[Music_Playlists] ON DELETE CASCADE
    )
    GO
    I have a Concerts table that relates to Music_Playlists, and I also have a Users table that relates to Music_Playlists. Along with that, I have a type definition on the Music_Tracks table so identity the multiple classes of tracks that are possible. I think this will end up being a lot cleaner.
    INSERT INTO Jokes (statement) VALUES ('Here is my witty SQL signature');

Posting Permissions

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