Relationship design

[LMichel]

I want to make a database contening information about CD's.
Difficulty is that CD's can contain music or data
So, I've made a first table whith general CD information (title, copy number, location, ...).

And two other tables: one for music cd info, one for data cd information.
My question is how to link these tables together ?

I want to able to list title of all CD's at the same time(music and data).

 

[tlbroadbent]

Use UNION query to obtain records from two JOIN queries.

Select g.title, g.[copy number], g.location, m.description
From GeneralTbl g Inner Join MusicTbl m
Union
Select g.title, g.[copy number], g.location, d.description
From GeneralTbl g Inner Join DataTbl d
Order By g.Title, g.[copy number]

NOTE: Both join queries must contain the same number of columns in the same select order with similar data types. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[LMichel]

Ok, but how do I define relationship between GeneralTbl, MusicTbl and DataTbl ?

Should I have something like this ?

GeneralTbl
CD_ID autonumber
Title text
Copy_num number
location text
Content_ID number


MusicTbl
Mus_ID autonumber
Artist text
Num_tracks number
Length number

DataTbl
Data_ID autonumber
Type text
Size number

Is it a good idea to link Content_ID whith Data_ID and Music_ID. What kind of link should I use ?
Content_id should sometimes reffer to Music_ID and somethimes to Data_ID. But not both at the same time.

 

[tlbroadbent]

Whoops! I forgot the ON clause in the JOINS. Sorry about that. I assumed that each table would carry a CD_ID or similar column that would be the basis for the relationship.
[tt]
GeneralTbl
CD_ID integer (Could be autonumber if you want. Some prefer to not use autonumber for a key field.)
Title text
Copy_num number
location text
Content_ID number

MusicTbl
Mus_ID autonumber
Artist text
Num_tracks number
CD_Length number
CD_ID integer

DataTbl
Data_ID autonumber
Date_Type text
Data_Size number
CD_ID integer

Select g.Title, g.Copy_Num, g.Location, m.Artist As Description, Num_Tracks, CD_Length, 'Music' As CD_Type
From GeneralTbl g Inner Join MusicTbl m
On g.CD_ID=m.CD_ID
Union
Select g.Title, g.Copy_Num, g.Location, d.Data_Type, 0, d.Data_Size, 'Data'
From GeneralTbl g Inner Join DataTbl d
On g.CD_ID=d.CD_ID
Order By g.Title, g.[copy number][/tt]

I don't believe the Content_ID column is necessary. I wouldn't use it for linking but it may be useful for identifying types of CDs rather than the literals, 'Music' and 'Data' that I added to the query. Terry
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[LMichel]

Thanks for your help
My problem is that one content description (MusicTbl or DataTbl) is related to several CD. In your examble one description refers only to one CD.

 

[tlbroadbent]

LMichel,

I'm not quite following you on the content description. Could you provide a couple of examples? Show how CDs are related through content description. I usually visualize things better with concrete examples.

Thanks, Terry
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[LMichel]

Ok, I'll let you know what I want to see on the screen.

First screen (CD information):

CD name
Copy number
location
Content

Second screen (describes the content of a CD that contains music)

Artist
Num_tracks
Length

Third screen

Type of data
Size

-----------------------------------------------------------

Exemples

CD: CDMuS01
Copy: 1
location: home
Content: Eric Clapton - unplugged

CD: CDMuS02
Copy: 2
location: office
Content: Eric Clapton - unplugged

Content description
Name: Eric Clapton - unplugged
Artist:Eric Clapton
Num_tracks: 12
Length: 60 min.

-----------------------------------------------
CD: CDDat01
Copy: 1
location: home
Content: Templates for Word

CD: CDat02
Copy: 2
location: office
Content: Templates for word

Content description
Name: Templates for word from management
Type of data: Dot files
Size: 250 Mb
----------------------------------------------
Hope this will clarify !