new database

new database

Post by vageli » Tue, 15 Jul 2003 03:29:54



i have to make an access database
with 3 (or more) tables
1st client
2nd salesman
3rd stock

my relationship is :    one salesman   to    many clients
But what about stock
I must use stock also in my database :)

any suggestions?

thanks in advance

 
 
 

new database

Post by James & Carla Bus » Thu, 17 Jul 2003 22:41:09


This sounds like a homework question, and if we tell you the answer, you'll
never learn, which won't help you, and it might make any apps that you design
error prone, difficult to use/maintain.

salesman to client is obviously one to many, as you say because one salesman
usually has many clients. That's the easy one. Stock is more difficult because
whose stock is it? Is it the client's? or the salesman's?

Suppose it's the salesman's stock. Does each salesman have his own stock items,
or is there an overlap between salesmen and stock? That would mean it could me
one to many, or many to many. The same if it's the client's stock.

Or is it stock that the salesman wants to sell to the client? In which case you
would have to relate salesman to stock to client, and have either one to many
or many to many relationships (as per above paragraph).

The other thing to consider is how is the data going to be used. Usually in
situations like this, there is another table called "Orders" or something like
that. The client places an order, and each order has a salesman. In this
situation there is many to many relationships and one to one relationships
going on.

Have fun.


> i have to make an access database
> with 3 (or more) tables
> 1st client
> 2nd salesman
> 3rd stock

> my relationship is :    one salesman   to    many clients
> But what about stock
> I must use stock also in my database :)


 
 
 

new database

Post by Sundial Service » Fri, 18 Jul 2003 07:54:53


My suggestion is:  "Stock" is "Stock," no matter who owns it.  "Stock"
is a Thing.  Therefore it has a table that simply describes the Thing.

Ditto salesman and clients:  they are Things.

"Ownership" is a Relationship; in this case a Many-to-Many
relationship.  Relationships (note: I am =not= using the specialized
MS-Access meaning of the term! ! !) imply the existence of more tables
whose only purpose is to describe them.

For example, a piece of stock is a piece of stock whether it's owned or
not.  (A database needs to be able to describe it, owned or not.)  A
second table might link Customers with Stock.  And so on.


> This sounds like a homework question, and if we tell you the answer, you'll
> never learn, which won't help you, and it might make any apps that you design
> error prone, difficult to use/maintain.

> salesman to client is obviously one to many, as you say because one salesman
> usually has many clients. That's the easy one. Stock is more difficult because
> whose stock is it? Is it the client's? or the salesman's?

> Suppose it's the salesman's stock. Does each salesman have his own stock items,
> or is there an overlap between salesmen and stock? That would mean it could me
> one to many, or many to many. The same if it's the client's stock.

> Or is it stock that the salesman wants to sell to the client? In which case you
> would have to relate salesman to stock to client, and have either one to many
> or many to many relationships (as per above paragraph).

> The other thing to consider is how is the data going to be used. Usually in
> situations like this, there is another table called "Orders" or something like
> that. The client places an order, and each order has a salesman. In this
> situation there is many to many relationships and one to one relationships
> going on.

> Have fun.


> > i have to make an access database
> > with 3 (or more) tables
> > 1st client
> > 2nd salesman
> > 3rd stock

> > my relationship is :    one salesman   to    many clients
> > But what about stock
> > I must use stock also in my database :)