Help! Complicated Relationships

[James]

Hello Everyone!

I'm trying to design a database for a library that stocks a range
of media. (see link)

http://homepage.ntlworld.com/james.merrie/private/library.gif

Each user can make many enquiries / reservations / loans
(if I can get enquiries right, I can do the rest). Every Enquiry
can be made on each item of stock, every item of stock is
a copy and every copy contains item info. Each item can be
of audio, book or score (musical score).

I would really appreciate any observations or advice where I'm
going right / where I'm going wrong. I would really appreciate
help with what queries I should make which will make it possible
eventually for a User to make an enquiry about any item of audio.

Thankyou for reading,

James

[Peter Steimann[MVP Access]]

Re: Help! Complicated Relationships

Hi James

"James" <james.merry@nt lworld.com> schrieb im Newsbeitrag
news:XoQic.97$c y3.25@newsfe1-win...[color=blue]
> Hello Everyone!
>
> I'm trying to design a database for a library that stocks a range
> of media. (see link)
>
> http://homepage.ntlworld.com/james.m...te/library.gif[/color]

First of all, i would think a little about the naming-conventions
(tables and fields) tblUser looks much better than user-;)

Stay away from fieldnames like [E-Mail] or [Active Member?] and from
Spaces, "-" and ?. Makes life much easier in futuractions. (Queries,
sql)etc.

I miss the ID-Field (ID, PrimaryKey, Autonumber)

You have to work on your DBDesign: No Relation to the table Reservation?

Peter

[Robert]

Re: Help! Complicated Relationships

When I look at your design I see that you have a function for "enquiry" and
no function for checking out books, so I'm thinking maybe an enquiry is when
you check out a book as opposed to inquiring about the existence of a book.
Is this correct?

You left out copynumber in audio and copynumber should be a PK if barcode is
not unique for each copy.

Robert

"James" <james.merry@nt lworld.com> wrote in message
news:XoQic.97$c y3.25@newsfe1-win...[color=blue]
> Hello Everyone!
>
> I'm trying to design a database for a library that stocks a range
> of media. (see link)
>
> http://homepage.ntlworld.com/james.m...te/library.gif
>
> Each user can make many enquiries / reservations / loans
> (if I can get enquiries right, I can do the rest). Every Enquiry
> can be made on each item of stock, every item of stock is
> a copy and every copy contains item info. Each item can be
> of audio, book or score (musical score).
>
> I would really appreciate any observations or advice where I'm
> going right / where I'm going wrong. I would really appreciate
> help with what queries I should make which will make it possible
> eventually for a User to make an enquiry about any item of audio.
>
> Thankyou for reading,
>
> James
>
>
>[/color]

[John Winterbottom]

Re: Help! Complicated Relationships

"Peter Steimann[MVP Access]" <PSteimann.News @Timesoft.ch> wrote in message
news:c6gs6d$b8p d1$2@ID-93817.news.uni-berlin.de...[color=blue]
> Hi James
>
> "James" <james.merry@nt lworld.com> schrieb im Newsbeitrag
> news:XoQic.97$c y3.25@newsfe1-win...[color=green]
> > Hello Everyone!
> >
> > I'm trying to design a database for a library that stocks a range
> > of media. (see link)
> >
> > http://homepage.ntlworld.com/james.m...te/library.gif[/color]
>
> First of all, i would think a little about the naming-conventions
> (tables and fields) tblUser looks much better than user-;)[/color]


Peter, I think "user" is just fine for a table of users - if there will only
ever be one of them. Otherwise i'd go with "users".
"tblUser" is not good - there's no valid reason to use "tbl" "qry" prefixes
for database object names.

--
btw if this were a table of linux users do you think we could we call it
"lusers" <g>

[James]

Re: Help! Complicated Relationships

Hello Robert,

Firstly, thankyou very much for replying to my cry for help!
I'm keen to emphasise that any comments/advice I receive
is very much appreciated.

Apologies for the vague post; I must admit, it isn't the
clearest post I've ever made!

The database I'm creating has (for my knowledge) developed
a lot since 4 o'clock today. Please check:

http://homepage.ntlworld.com/james.merrie/private/library.gif

as I've updated the image of the relationships in my database.

As far and Enquiries and Audio go, everything seems to work ok!! :oD
I'm thinking that the rest of the database will be easy once I've
worked out how I'm going to work with it in its present state.
This is why I left out any mention of other tables because I
wanted to concentrate on the enquiry-audio part of the database
(hope this makes sense!). The enquiries made by users are just
'points of interest' (It's pretty pointless but I had to include it), users
can make an enquiry on any item of stock (to find out whether its
on loan etc). Other actions the user can perform are:
2. reserving an item and 3. loaning or borrowing an item.
Of course, each user can have multiple enquiries, multiple reservations
and multiple loans.
The 'catalogue' as I'm sure you've guessed, is a way of controlling
each unique item by type: each item can either be a 'audio,' 'book,'
or 'Score.' I'm hoping to connect all these tables in the same fashion as
'audio' is connected now. I think this will work, what do you think ????

Naming conventions etc have been intentionally ignored because
I feel that the most important thing is to get the model working.

Ok, I hope that make things a little clearer. Let me know your
thoughts and I'll try to help you to help me!

Thanks Robert,


James



"Robert" <projecttoday@y ahoo.com> wrote in message
news:108nt4kggh jgr56@corp.supe rnews.com...[color=blue]
> When I look at your design I see that you have a function for "enquiry"[/color]
and[color=blue]
> no function for checking out books, so I'm thinking maybe an enquiry is[/color]
when[color=blue]
> you check out a book as opposed to inquiring about the existence of a[/color]
book.[color=blue]
> Is this correct?
>
> You left out copynumber in audio and copynumber should be a PK if barcode[/color]
is[color=blue]
> not unique for each copy.
>
> Robert
>
> "James" <james.merry@nt lworld.com> wrote in message
> news:XoQic.97$c y3.25@newsfe1-win...[color=green]
> > Hello Everyone!
> >
> > I'm trying to design a database for a library that stocks a range
> > of media. (see link)
> >
> > http://homepage.ntlworld.com/james.m...te/library.gif
> >
> > Each user can make many enquiries / reservations / loans
> > (if I can get enquiries right, I can do the rest). Every Enquiry
> > can be made on each item of stock, every item of stock is
> > a copy and every copy contains item info. Each item can be
> > of audio, book or score (musical score).
> >
> > I would really appreciate any observations or advice where I'm
> > going right / where I'm going wrong. I would really appreciate
> > help with what queries I should make which will make it possible
> > eventually for a User to make an enquiry about any item of audio.
> >
> > Thankyou for reading,
> >
> > James
> >
> >
> >[/color]
>
>[/color]

[James]

Re: Help! Complicated Relationships

Hi Peter,

Thanks for your reply.

I've just updated the image I captured of database relationships.
Please check again.

The naming conventions will be important, I did forget about
them earlier on nevertheless thankyou for reminding me! :o)

I wanted to concentrate on enquiries for audio as I think that
if I were to get this right, I could do the rest. As it stands,
everything appears to work! horray! However, I will probably
need more help so I've made a post lower down that makes
things clearer, sorry I can't type it all out again - my fingers
are beginning to ache!

James


"Peter Steimann[MVP Access]" <PSteimann.News @Timesoft.ch> wrote in message
news:c6gs6d$b8p d1$2@ID-93817.news.uni-berlin.de...[color=blue]
> Hi James
>
> "James" <james.merry@nt lworld.com> schrieb im Newsbeitrag
> news:XoQic.97$c y3.25@newsfe1-win...[color=green]
> > Hello Everyone!
> >
> > I'm trying to design a database for a library that stocks a range
> > of media. (see link)
> >
> > http://homepage.ntlworld.com/james.m...te/library.gif[/color]
>
> First of all, i would think a little about the naming-conventions
> (tables and fields) tblUser looks much better than user-;)
>
> Stay away from fieldnames like [E-Mail] or [Active Member?] and from
> Spaces, "-" and ?. Makes life much easier in futuractions. (Queries,
> sql)etc.
>
> I miss the ID-Field (ID, PrimaryKey, Autonumber)
>
> You have to work on your DBDesign: No Relation to the table Reservation?
>
> Peter[/color]

[Dave Brydon]

Re: Help! Complicated Relationships

Hi James,

I built a library database a while back....if your interested I can send you
a copy for review, it may be of some help. Let me know which version of
Access your using.


Cheers,

Dave

"James" <james.merry@nt lworld.com> wrote in message
news:XoQic.97$c y3.25@newsfe1-win...[color=blue]
> Hello Everyone!
>
> I'm trying to design a database for a library that stocks a range
> of media. (see link)
>
> http://homepage.ntlworld.com/james.m...te/library.gif
>
> Each user can make many enquiries / reservations / loans
> (if I can get enquiries right, I can do the rest). Every Enquiry
> can be made on each item of stock, every item of stock is
> a copy and every copy contains item info. Each item can be
> of audio, book or score (musical score).
>
> I would really appreciate any observations or advice where I'm
> going right / where I'm going wrong. I would really appreciate
> help with what queries I should make which will make it possible
> eventually for a User to make an enquiry about any item of audio.
>
> Thankyou for reading,
>
> James
>
>
>[/color]

[James]

Re: Help! Complicated Relationships

Hi Dave,

I'm using Access 2002 (think its part of Office XP).
If you could send it that would be excellent.
My Email is: james.merri@ntl world.com

Many thanks,

James



"Dave Brydon" <dbrydon@ns.sym patico.ca> wrote in message
news:iPTic.2910 6$Np3.1032079@u rsa-nb00s0.nbnet.nb .ca...[color=blue]
> Hi James,
>
> I built a library database a while back....if your interested I can send[/color]
you[color=blue]
> a copy for review, it may be of some help. Let me know which version of
> Access your using.
>
>
> Cheers,
>
> Dave
>
> "James" <james.merry@nt lworld.com> wrote in message
> news:XoQic.97$c y3.25@newsfe1-win...[color=green]
> > Hello Everyone!
> >
> > I'm trying to design a database for a library that stocks a range
> > of media. (see link)
> >
> > http://homepage.ntlworld.com/james.m...te/library.gif
> >
> > Each user can make many enquiries / reservations / loans
> > (if I can get enquiries right, I can do the rest). Every Enquiry
> > can be made on each item of stock, every item of stock is
> > a copy and every copy contains item info. Each item can be
> > of audio, book or score (musical score).
> >
> > I would really appreciate any observations or advice where I'm
> > going right / where I'm going wrong. I would really appreciate
> > help with what queries I should make which will make it possible
> > eventually for a User to make an enquiry about any item of audio.
> >
> > Thankyou for reading,
> >
> > James
> >
> >
> >[/color]
>
>[/color]

[Robert]

Re: Help! Complicated Relationships

Okay. I see that you have a separate item Id and item barcode and item copy
number. I didn't see that at first. Each individual item in the library
can be identified with the item id or the combination of item barcode + copy
number. So the copy number in the Book table is not needed. It looked to
me at first like you had forgotten to put the copy number in the audio
table. Having 2 ways of identifying a book is redundant, but it might be
easier for the people who handle the books to work with a single number (if
they even look at the number).

I think you're on the right track. Go for it!

Robert


"James" <james.merry@nt lworld.com> wrote in message
news:e_Sic.146$ cy3.82@newsfe1-win...[color=blue]
> Hello Robert,
>
> Firstly, thankyou very much for replying to my cry for help!
> I'm keen to emphasise that any comments/advice I receive
> is very much appreciated.
>
> Apologies for the vague post; I must admit, it isn't the
> clearest post I've ever made!
>
> The database I'm creating has (for my knowledge) developed
> a lot since 4 o'clock today. Please check:
> http://homepage.ntlworld.com/james.m...te/library.gif
> as I've updated the image of the relationships in my database.
>
> As far and Enquiries and Audio go, everything seems to work ok!! :oD
> I'm thinking that the rest of the database will be easy once I've
> worked out how I'm going to work with it in its present state.
> This is why I left out any mention of other tables because I
> wanted to concentrate on the enquiry-audio part of the database
> (hope this makes sense!). The enquiries made by users are just
> 'points of interest' (It's pretty pointless but I had to include it),[/color]
users[color=blue]
> can make an enquiry on any item of stock (to find out whether its
> on loan etc). Other actions the user can perform are:
> 2. reserving an item and 3. loaning or borrowing an item.
> Of course, each user can have multiple enquiries, multiple reservations
> and multiple loans.
> The 'catalogue' as I'm sure you've guessed, is a way of controlling
> each unique item by type: each item can either be a 'audio,' 'book,'
> or 'Score.' I'm hoping to connect all these tables in the same fashion as
> 'audio' is connected now. I think this will work, what do you think ????
>
> Naming conventions etc have been intentionally ignored because
> I feel that the most important thing is to get the model working.
>
> Ok, I hope that make things a little clearer. Let me know your
> thoughts and I'll try to help you to help me!
>
> Thanks Robert,
>
>
> James
>
>
>
> "Robert" <projecttoday@y ahoo.com> wrote in message
> news:108nt4kggh jgr56@corp.supe rnews.com...[color=green]
> > When I look at your design I see that you have a function for "enquiry"[/color]
> and[color=green]
> > no function for checking out books, so I'm thinking maybe an enquiry is[/color]
> when[color=green]
> > you check out a book as opposed to inquiring about the existence of a[/color]
> book.[color=green]
> > Is this correct?
> >
> > You left out copynumber in audio and copynumber should be a PK if[/color][/color]
barcode[color=blue]
> is[color=green]
> > not unique for each copy.
> >
> > Robert
> >
> > "James" <james.merry@nt lworld.com> wrote in message
> > news:XoQic.97$c y3.25@newsfe1-win...[color=darkred]
> > > Hello Everyone!
> > >
> > > I'm trying to design a database for a library that stocks a range
> > > of media. (see link)
> > >
> > > http://homepage.ntlworld.com/james.m...te/library.gif
> > >
> > > Each user can make many enquiries / reservations / loans
> > > (if I can get enquiries right, I can do the rest). Every Enquiry
> > > can be made on each item of stock, every item of stock is
> > > a copy and every copy contains item info. Each item can be
> > > of audio, book or score (musical score).
> > >
> > > I would really appreciate any observations or advice where I'm
> > > going right / where I'm going wrong. I would really appreciate
> > > help with what queries I should make which will make it possible
> > > eventually for a User to make an enquiry about any item of audio.
> > >
> > > Thankyou for reading,
> > >
> > > James
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[James]

Re: Help! Complicated Relationships

:oD Big Smiles!

Thanks for reminding me. I've made the changes (this
will also apply to table 'Score' which is yet another media type.

I've decided to migrate all attributes that all media types
have in common (i.e. a 'title' and a 'category') to the
catalogue. This should make things better. I've got until
Tuesday afternoon to complete this! agh!

Thanks for your help, and if you want, I can keep you
updated? (I might need the help :oP )


James



"Robert" <projecttoday@y ahoo.com> wrote in message
news:108o6q16dh 5pu49@corp.supe rnews.com...[color=blue]
> Okay. I see that you have a separate item Id and item barcode and item[/color]
copy[color=blue]
> number. I didn't see that at first. Each individual item in the library
> can be identified with the item id or the combination of item barcode +[/color]
copy[color=blue]
> number. So the copy number in the Book table is not needed. It looked to
> me at first like you had forgotten to put the copy number in the audio
> table. Having 2 ways of identifying a book is redundant, but it might be
> easier for the people who handle the books to work with a single number[/color]
(if[color=blue]
> they even look at the number).
>
> I think you're on the right track. Go for it!
>
> Robert
>
>
> "James" <james.merry@nt lworld.com> wrote in message
> news:e_Sic.146$ cy3.82@newsfe1-win...[color=green]
> > Hello Robert,
> >
> > Firstly, thankyou very much for replying to my cry for help!
> > I'm keen to emphasise that any comments/advice I receive
> > is very much appreciated.
> >
> > Apologies for the vague post; I must admit, it isn't the
> > clearest post I've ever made!
> >
> > The database I'm creating has (for my knowledge) developed
> > a lot since 4 o'clock today. Please check:
> > http://homepage.ntlworld.com/james.m...te/library.gif
> > as I've updated the image of the relationships in my database.
> >
> > As far and Enquiries and Audio go, everything seems to work ok!! :oD
> > I'm thinking that the rest of the database will be easy once I've
> > worked out how I'm going to work with it in its present state.
> > This is why I left out any mention of other tables because I
> > wanted to concentrate on the enquiry-audio part of the database
> > (hope this makes sense!). The enquiries made by users are just
> > 'points of interest' (It's pretty pointless but I had to include it),[/color]
> users[color=green]
> > can make an enquiry on any item of stock (to find out whether its
> > on loan etc). Other actions the user can perform are:
> > 2. reserving an item and 3. loaning or borrowing an item.
> > Of course, each user can have multiple enquiries, multiple reservations
> > and multiple loans.
> > The 'catalogue' as I'm sure you've guessed, is a way of controlling
> > each unique item by type: each item can either be a 'audio,' 'book,'
> > or 'Score.' I'm hoping to connect all these tables in the same fashion[/color][/color]
as[color=blue][color=green]
> > 'audio' is connected now. I think this will work, what do you think ????
> >
> > Naming conventions etc have been intentionally ignored because
> > I feel that the most important thing is to get the model working.
> >
> > Ok, I hope that make things a little clearer. Let me know your
> > thoughts and I'll try to help you to help me!
> >
> > Thanks Robert,
> >
> >
> > James
> >
> >
> >
> > "Robert" <projecttoday@y ahoo.com> wrote in message
> > news:108nt4kggh jgr56@corp.supe rnews.com...[color=darkred]
> > > When I look at your design I see that you have a function for[/color][/color][/color]
"enquiry"[color=blue][color=green]
> > and[color=darkred]
> > > no function for checking out books, so I'm thinking maybe an enquiry[/color][/color][/color]
is[color=blue][color=green]
> > when[color=darkred]
> > > you check out a book as opposed to inquiring about the existence of a[/color]
> > book.[color=darkred]
> > > Is this correct?
> > >
> > > You left out copynumber in audio and copynumber should be a PK if[/color][/color]
> barcode[color=green]
> > is[color=darkred]
> > > not unique for each copy.
> > >
> > > Robert
> > >
> > > "James" <james.merry@nt lworld.com> wrote in message
> > > news:XoQic.97$c y3.25@newsfe1-win...
> > > > Hello Everyone!
> > > >
> > > > I'm trying to design a database for a library that stocks a range
> > > > of media. (see link)
> > > >
> > > > http://homepage.ntlworld.com/james.m...te/library.gif
> > > >
> > > > Each user can make many enquiries / reservations / loans
> > > > (if I can get enquiries right, I can do the rest). Every Enquiry
> > > > can be made on each item of stock, every item of stock is
> > > > a copy and every copy contains item info. Each item can be
> > > > of audio, book or score (musical score).
> > > >
> > > > I would really appreciate any observations or advice where I'm
> > > > going right / where I'm going wrong. I would really appreciate
> > > > help with what queries I should make which will make it possible
> > > > eventually for a User to make an enquiry about any item of audio.
> > > >
> > > > Thankyou for reading,
> > > >
> > > > James
> > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Peter Steimann[MVP Access]]

Re: Help! Complicated Relationships

Hi John

"John Winterbottom" <assaynet@hotma il.com> schrieb im Newsbeitrag
news:c6gtae$bkb 2s$1@ID-185006.news.uni-berlin.de...[color=blue]
> "Peter Steimann[MVP Access]" <PSteimann.News @Timesoft.ch> wrote in message
> news:c6gs6d$b8p d1$2@ID-93817.news.uni-berlin.de...[color=green]
> > Hi James
> >
> > "James" <james.merry@nt lworld.com> schrieb im Newsbeitrag
> > news:XoQic.97$c y3.25@newsfe1-win...[color=darkred]
> > > Hello Everyone!
> > >
> > > I'm trying to design a database for a library that stocks a range
> > > of media. (see link)
> > >
> > > http://homepage.ntlworld.com/james.m...te/library.gif[/color]
> >
> > First of all, i would think a little about the naming-conventions
> > (tables and fields) tblUser looks much better than user-;)[/color]
>
>
> Peter, I think "user" is just fine for a table of users - if there will only
> ever be one of them. Otherwise i'd go with "users".
> "tblUser" is not good - there's no valid reason to use "tbl" "qry" prefixes
> for database object names.[/color]

You can name then however you wan't. Using naming-conventions makes life
easier. That's my experience.

--[color=blue]
> btw if this were a table of linux users do you think we could we call it
> "lusers" <g>[/color]

A user is a user...

Peter










[color=blue]
>
>
>
>[/color]

[James Fortune]

Re: Help! Complicated Relationships

"John Winterbottom" <assaynet@hotma il.com> wrote in message news:<c6gtae$bk b2s$1@ID-185006.news.uni-berlin.de>...[color=blue]
> Peter, I think "user" is just fine for a table of users - if there will only
> ever be one of them. Otherwise i'd go with "users".
> "tblUser" is not good - there's no valid reason to use "tbl" "qry" prefixes
> for database object names.[/color]

You shouldn't be allowed to be your own straight man :-). I could be
pedantic about this and quote some "lusers":

By common entity naming conventions, an entity name must be singular.
We therefore call the table where we store CDs "CD" and not "CDs." We
use this convention because each entity names an instance. For
example, the "San Francisco 49ers" is an instance of "Football Team,"
not "Football Teams."

MySQL and mSQL
Yarger, Reese and King
O'Reilly 1999

But I don't follow this convention myself even though I am a luser,
err.. instance of lusers, err.. listed in lusers. Although you are
correct about there being no pragmatic reasons for tbl and qry
prefixes (except maybe no duplicate names being allowed -- I like
associating qryData with tblData), it is easier for repliers and
others on the newsgroup to decipher what's going on when someone posts
their Code/SQL if they have such prefixes. I try not to get dogmatic
about naming conventions. Controls, OTOH, sometimes lend themselves
to easier solutions when a naming convention is used. For me, I think
clients really like to see what looks like organization so I'll keep
the tbl and qry prefixes.

James A. Fortune

[John Winterbottom]

Re: Help! Complicated Relationships

"James Fortune" <jafortun@oakla nd.edu> wrote in message
news:a6ed3ce7.0 404251414.6e6e8 6d3@posting.goo gle.com...[color=blue]
> "John Winterbottom" <assaynet@hotma il.com> wrote in message[/color]
news:<c6gtae$bk b2s$1@ID-185006.news.uni-berlin.de>...[color=blue][color=green]
> > Peter, I think "user" is just fine for a table of users - if there will[/color][/color]
only[color=blue][color=green]
> > ever be one of them. Otherwise i'd go with "users".
> > "tblUser" is not good - there's no valid reason to use "tbl" "qry"[/color][/color]
prefixes[color=blue][color=green]
> > for database object names.[/color]
>
> You shouldn't be allowed to be your own straight man :-). I could be
> pedantic about this and quote some "lusers":
>
> By common entity naming conventions, an entity name must be singular.
> We therefore call the table where we store CDs "CD" and not "CDs." We
> use this convention because each entity names an instance. For
> example, the "San Francisco 49ers" is an instance of "Football Team,"
> not "Football Teams."
>[/color]


Entities are sets, or collections, of things, so it is logical to name them
in the plural.

[Robert]

Re: Help! Complicated Relationships

You're welcome. Yes, keep me updated.

Robert

"James" <james.merry@nt lworld.com> wrote in message
news:VNVic.200$ cy3.134@newsfe1-win...[color=blue]
> :oD Big Smiles!
>
> Thanks for reminding me. I've made the changes (this
> will also apply to table 'Score' which is yet another media type.
>
> I've decided to migrate all attributes that all media types
> have in common (i.e. a 'title' and a 'category') to the
> catalogue. This should make things better. I've got until
> Tuesday afternoon to complete this! agh!
>
> Thanks for your help, and if you want, I can keep you
> updated? (I might need the help :oP )
>
>
> James
>
>
>
> "Robert" <projecttoday@y ahoo.com> wrote in message
> news:108o6q16dh 5pu49@corp.supe rnews.com...[color=green]
> > Okay. I see that you have a separate item Id and item barcode and item[/color]
> copy[color=green]
> > number. I didn't see that at first. Each individual item in the[/color][/color]
library[color=blue][color=green]
> > can be identified with the item id or the combination of item barcode +[/color]
> copy[color=green]
> > number. So the copy number in the Book table is not needed. It looked[/color][/color]
to[color=blue][color=green]
> > me at first like you had forgotten to put the copy number in the audio
> > table. Having 2 ways of identifying a book is redundant, but it might[/color][/color]
be[color=blue][color=green]
> > easier for the people who handle the books to work with a single number[/color]
> (if[color=green]
> > they even look at the number).
> >
> > I think you're on the right track. Go for it!
> >
> > Robert
> >
> >
> > "James" <james.merry@nt lworld.com> wrote in message
> > news:e_Sic.146$ cy3.82@newsfe1-win...[color=darkred]
> > > Hello Robert,
> > >
> > > Firstly, thankyou very much for replying to my cry for help!
> > > I'm keen to emphasise that any comments/advice I receive
> > > is very much appreciated.
> > >
> > > Apologies for the vague post; I must admit, it isn't the
> > > clearest post I've ever made!
> > >
> > > The database I'm creating has (for my knowledge) developed
> > > a lot since 4 o'clock today. Please check:
> > > http://homepage.ntlworld.com/james.m...te/library.gif
> > > as I've updated the image of the relationships in my database.
> > >
> > > As far and Enquiries and Audio go, everything seems to work ok!! :oD
> > > I'm thinking that the rest of the database will be easy once I've
> > > worked out how I'm going to work with it in its present state.
> > > This is why I left out any mention of other tables because I
> > > wanted to concentrate on the enquiry-audio part of the database
> > > (hope this makes sense!). The enquiries made by users are just
> > > 'points of interest' (It's pretty pointless but I had to include it),[/color]
> > users[color=darkred]
> > > can make an enquiry on any item of stock (to find out whether its
> > > on loan etc). Other actions the user can perform are:
> > > 2. reserving an item and 3. loaning or borrowing an item.
> > > Of course, each user can have multiple enquiries, multiple[/color][/color][/color]
reservations[color=blue][color=green][color=darkred]
> > > and multiple loans.
> > > The 'catalogue' as I'm sure you've guessed, is a way of controlling
> > > each unique item by type: each item can either be a 'audio,' 'book,'
> > > or 'Score.' I'm hoping to connect all these tables in the same fashion[/color][/color]
> as[color=green][color=darkred]
> > > 'audio' is connected now. I think this will work, what do you think[/color][/color][/color]
????[color=blue][color=green][color=darkred]
> > >
> > > Naming conventions etc have been intentionally ignored because
> > > I feel that the most important thing is to get the model working.
> > >
> > > Ok, I hope that make things a little clearer. Let me know your
> > > thoughts and I'll try to help you to help me!
> > >
> > > Thanks Robert,
> > >
> > >
> > > James
> > >
> > >
> > >
> > > "Robert" <projecttoday@y ahoo.com> wrote in message
> > > news:108nt4kggh jgr56@corp.supe rnews.com...
> > > > When I look at your design I see that you have a function for[/color][/color]
> "enquiry"[color=green][color=darkred]
> > > and
> > > > no function for checking out books, so I'm thinking maybe an enquiry[/color][/color]
> is[color=green][color=darkred]
> > > when
> > > > you check out a book as opposed to inquiring about the existence of[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > > book.
> > > > Is this correct?
> > > >
> > > > You left out copynumber in audio and copynumber should be a PK if[/color]
> > barcode[color=darkred]
> > > is
> > > > not unique for each copy.
> > > >
> > > > Robert
> > > >
> > > > "James" <james.merry@nt lworld.com> wrote in message
> > > > news:XoQic.97$c y3.25@newsfe1-win...
> > > > > Hello Everyone!
> > > > >
> > > > > I'm trying to design a database for a library that stocks a range
> > > > > of media. (see link)
> > > > >
> > > > > http://homepage.ntlworld.com/james.m...te/library.gif
> > > > >
> > > > > Each user can make many enquiries / reservations / loans
> > > > > (if I can get enquiries right, I can do the rest). Every Enquiry
> > > > > can be made on each item of stock, every item of stock is
> > > > > a copy and every copy contains item info. Each item can be
> > > > > of audio, book or score (musical score).
> > > > >
> > > > > I would really appreciate any observations or advice where I'm
> > > > > going right / where I'm going wrong. I would really appreciate
> > > > > help with what queries I should make which will make it possible
> > > > > eventually for a User to make an enquiry about any item of audio.
> > > > >
> > > > > Thankyou for reading,
> > > > >
> > > > > James
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[rkc]

Re: Help! Complicated Relationships


"John Winterbottom" <assaynet@hotma il.com> wrote in message
news:c6iupn$brr ao$1@ID-185006.news.uni-berlin.de...[color=blue]
> "James Fortune" <jafortun@oakla nd.edu> wrote in message
> news:a6ed3ce7.0 404251414.6e6e8 6d3@posting.goo gle.com...[color=green]
> > "John Winterbottom" <assaynet@hotma il.com> wrote in message[/color]
> news:<c6gtae$bk b2s$1@ID-185006.news.uni-berlin.de>...[color=green][color=darkred]
> > > Peter, I think "user" is just fine for a table of users - if there[/color][/color][/color]
will[color=blue]
> only[color=green][color=darkred]
> > > ever be one of them. Otherwise i'd go with "users".
> > > "tblUser" is not good - there's no valid reason to use "tbl" "qry"[/color][/color]
> prefixes[color=green][color=darkred]
> > > for database object names.[/color]
> >
> > You shouldn't be allowed to be your own straight man :-). I could be
> > pedantic about this and quote some "lusers":
> >
> > By common entity naming conventions, an entity name must be singular.
> > We therefore call the table where we store CDs "CD" and not "CDs." We
> > use this convention because each entity names an instance. For
> > example, the "San Francisco 49ers" is an instance of "Football Team,"
> > not "Football Teams."
> >[/color]
>
>
> Entities are sets, or collections, of things, so it is logical to name[/color]
them[color=blue]
> in the plural.[/color]

A single row in a table is not an entities.

SELECT * FROM Employee WHERE EmployeeID = 1 doesn't
return a set of Employees.

If you Google this subject you can read for hours and still just have
to make up your own mind.