Help! Complicated Relationship (Part 2)

**Ray** · Nov 12 '05, 11:06 PM

Re: Help! Complicated Relationship (Part 2)

"James" <james.merry@nt lworld.com> wrote in message news:<MVYic.241 $Od6.205@newsfe 5-gui.server.ntli .net>...[color=blue]
> 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[/color]

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

**Phil Stanton** · Nov 12 '05, 11:07 PM

Re: Help! Complicated Relationship (Part 2)

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" <raycait@excite .com> wrote in message
news:20ba9f4a.0 404260516.6b2f5 ec1@posting.goo gle.com...[color=blue]
> "James" <james.merry@nt lworld.com> wrote in message[/color]
news:<MVYic.241 $Od6.205@newsfe 5-gui.server.ntli .net>...[color=blue][color=green]
> > 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[/color]
>
> 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[/color]

Help! Complicated Relationship (Part 2)

Help! Complicated Relationship (Part 2)

Comment

Comment