Quick table setup question

[Grant2012]

Hi, I wonder of somebody would be good enough to give me a little bit of assistance.



I am creating a DB to log my clients and to record what parts they send me.

My clients own music which they ask me to handle the manufacturing of their optical discs, CD's DVD etc.

I need to know the best way to set up my tables (tblCustomers, tblTitles & tblMasters and Relationships in order to accurately record which clients own which Titles and in turn which masters are associated with a particular title.

Each title has a unique reference (eg. abc123) and a master would have the same reference if there is only one master or the same Cat reference preceded with a /1, /2, /3 etc for more than one master.

Would it be better to have the Unique reference (PK) for the Titles Table (tblTitles) set as the Catalogue reference and the same for the Master Table (tblMasters)? but then how would I relate these to the correct Customer so that the master(s) are related to the correct Title and the Title related to the correct Customer?

Thanks for any assistance on this.

Grant

[ssanfu]

So one customer can have zero or more titles and one title can have zero or more masters?
This is how I would start (see attached PDF)

Without knowing more about the field types or seeing data in the tables, it is hard to provide more....

[Grant2012]

Thanks Steve, I really appreciate the assistance.

We wouldn't have a Customer that doesn't own a Title or associated Masters, Customers would have at least 1 Title but could have many more

I'll give this a try, essentially I need to be able to keep track of which Title belong to which Customers and which Masters are used to create which Titles.
The unique identifier for any Title or Master is a Catalogue Reference (eg. DADCD001) but a Title could have 1 or more Masters (most I've done is 10).

I want to be able to design a Form that lets me enter the Title information, (CustomerID (owner),
TitleCat Reference (eg. DADCD001),
TitleId (eg. Songs for Dads)
and a couple of other fields. That Form will allow me to add the data to the tblTitles

Then I do the same to log the associated Masters into the tblMasters, these then have to be related to a title and in turn to the Customer.

So, those 3 tables are very important in order to get everything else correct and working properly.


Do you think the Relationship you suggested would allow me to do this or would it be better if I showed you an example of the data I currently have?

Many thanks again

Grant

[ssanfu]

Do you think the Relationship you suggested would allow me to do this or would it be better if I showed you an example of the data I currently have?

It always helps to see the data.. and to know the data types of the fields as you see the design. If you attach a dB, note that my Access version is A2000 .

I want to be able to design a Form that lets me enter the Title information,

You need a form to enter the Customer info, a form to enter the Title info and a form to enter the Master info. This is at a minimum.... maybe more forms.
The form record sources should be a query.
You could use the form to enter the Title info to also enter the Master info - a form-subform arrangement. I use those a lot.