| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
01-05-06, 14:20
|
|
Registered User
|
|
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.
__________________
INSERT INTO Jokes (statement) VALUES ('Here is my witty SQL signature');
|
|
01-05-06, 14:44
|
|
Purveyor of Discontent
|
|
Join Date: Mar 2003
Location: The Bottom of The Barrel
Posts: 6,075
|
|
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.
|
|
01-05-06, 14:53
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
|
|
|
|
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
|
|
01-05-06, 18:02
|
|
Registered User
|
|
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');
|
|
01-09-06, 02:58
|
|
Registered User
|
|
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.
|
|
01-13-06, 14:24
|
|
Registered User
|
|
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');
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
IsA relationships
