By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 983 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Help! Complicated Relationship (Part 2)

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Ray
"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
Nov 12 '05 #2

P: n/a
Hi James

Seems you have too many tables and ar also missing tables.

I would suggest that you hava a table for MediaType eg Book, Score, Tape, CD
Record 78rpm, Record 45 RPM Record LP etc.The current tables for Audio,
Books and Scores can then be combined with a pointer to the MediaType table.
You will then realize that there is no difference between an author of a
book or a composer of music so that a table of composers and authors should
be created (you can have all sorts of ancilliary information like born,
died. country etc) you then can just pick the author or composer from a
combo box and hold the ComposerID as a FC in the Music Item. Similarly you
may consider treating the publisher and performer in the same way. I am sure
that a number of pieces of music will all have the same composer (Mozart or
the Beetles according to your taste), and I guess if you have to type the
same name in more than half a dozen times or so, you should think of holding
those names in a table.

What I am not clear on is whether you have multiple copies of any of your
items, and if so how are they differentiated?

"Ray" <ra*****@excite.com> wrote in message
news:20**************************@posting.google.c om...
"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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.