"James" <ja*********@ntlworld.com> wrote in message news:<MV***************@newsfe5-gui.server.ntli.net>...
Hello all,
In my music library database I have my stock categorised by
media type; the types of media are: audio, book and score.
My score type is the one I'm having difficulty with.
Each Score may contain many sets and each set may contain
may parts. Please can someone suggest what tables I would
need and what relationships would be required to make what
I have described work. It is not as easy as it sounds because
a catalogue prevents me from making simple one to many
relationships.
I have left an updated image of my database relationships on the
internet for helpful assistance:
http://homepage.ntlworld.com/james.m...te/library.gif
As you can see, I have got myself into a bit of a muddle.
Thankyou for reading. I look forward to any help I may receive,
James
Hello James,
The first thing I noticed in your diagramme is your use of the
Item Barcode ID. Not good. Perhaps you could work on that first
before normalizing your system totally.
First, in the top left had part of your picture, you have a table
called Item. Structure basically correct. All six tables that link
into it are not. First, the table called Catalogue perhaps should
be called "tblBarcode" since your Key field is called "Item Barcode".
After that, the field "Item Barcode" should be the second field in each
of the other tables since they are accessing the ID from the tblBarcode.
Example: tblAudio
1. txtAudioID - Autonumber - Key Field
2. txtItemBarcode - Number (Integer)
3. Media Type - Number (Integer) (This will hold the ID from tblMediaType
unless your using a combobox that has list values.)
4. etc..
You'll notice that I put the Item Barcode field second. This is the field
that would link from the txtItemBarcode in the tblItemBarcode.
There should not be another table with the Item Barcode as it's key field.
Also one more thing, if your not aware, you should use naming conventions
in the naming of your tables, fields, queries, etc.
Example: Table = tblName
Field in table = txtName
Query = qryName
etc.
This will help keep things organized better and there are no spaces
in the names. The property caption can be used to have the name display
for your users according to what fits best.
As for your table requirements, I'll leave that for someone else.
Regards,
Ray