Very Computer

Hello group,

I'm setting up a little database to allow multiple users
to track their online auctions.

One of my requirements is that each user be able to
create their own "groups" to classify their auctions.

My problem is that one way of planning this creates
a "triangle".  Here are the tables (in brief) as an example
so you can see the relations

user
===
user_id  PK
email

auction
=====
auction_id  PK
user_id      FK
auction_group_id

auction_group
==========
auction_group_id PK
user_id                 FK
description

What you can see is that both auction tables
are related to the user table, and the two auction
tables are related.

This seems somehow wrong, but I am unclear as
to how I go about fixing this.  Actually I do have
an idea... perhaps the right idea so what do you
think about this:

user
===
user_id  PK
email

auction_group
==========
auction_group_id PK
user_id                 FK
auction_id            FK
description

auction
=====
auction_id  PK
auction_group_id

In this way, I can relate the auction to the user through
the auction_group.  Seems reasonable...

Well, I'd appreciate  any comments on this.
Thanks,
Trooper

05-April-2003
==========

Originally posted by Trooper

Quote:> Hello group,

> I'm setting up a little database to allow multiple users
> to track their online auctions.

> One of my requirements is that each user be able to
> create their own "groups" to classify their auctions.

> My problem is that one way of planning this creates
> a "triangle".  Here are the tables (in brief) as an example
> so you can see the relations

> user
> ===
> user_id  PK
> email

> auction
> =====
> auction_id  PK
> user_id      FK
> auction_group_id

> auction_group
> ==========
> auction_group_id PK
> user_id                 FK
> description

> What you can see is that both auction tables
> are related to the user table, and the two auction
> tables are related.

> This seems somehow wrong, but I am unclear as
> to how I go about fixing this.  Actually I do have
> an idea... perhaps the right idea so what do you
> think about this:

> user
> ===
> user_id  PK
> email

> auction_group
> ==========
> auction_group_id PK
> user_id                 FK
> auction_id            FK
> description

> auction
> =====
> auction_id  PK
> auction_group_id

> In this way, I can relate the auction to the user through
> the auction_group.  Seems reasonable...

> Well, I'd appreciate  any comments on this.
> Thanks,
> Trooper

> 05-April-2003
> ==========

Your second idea is wrong, as it means a group can only contain
one auction!

The first model is better, and it is not wrong.  It states correctly
that users can create auction groups, and users can create auctions, and
that the auctions may be assigned to auction groups.  That's OK.

--
Posted via http://dbforums.com

Quote:> > user
> > ===
> > user_id  PK
> > email

> > auction_group
> > ==========
> > auction_group_id PK
> > user_id                 FK
> > auction_id            FK
> > description

> > auction
> > =====
> > auction_id  PK
> > auction_group_id

> > In this way, I can relate the auction to the user through
> > the auction_group.  Seems reasonable...

> > Well, I'd appreciate  any comments on this.
> > Thanks,
> > Trooper

> > 05-April-2003
> > ==========
> Your second idea is wrong, as it means a group can only contain
> one auction!

> The first model is better, and it is not wrong.  It states correctly
> that users can create auction groups, and users can create auctions, and
> that the auctions may be assigned to auction groups.  That's OK.

Thank you for your reply.  And my appologies about that unfortunate
field in the auction_group table.  the auction_id field should not be in
there.  Once again:

user
===
user_id  PK
email

auction_group
==========
auction_group_id PK
user_id                 FK
description

auction
=====
auction_id  PK
auction_group_id

Now what I'm seeing is this.  Due to the fact that I want users to
be able to track auctions by group, this now makes sense because
it makes it manditory to have a group to put an auction into
(whether they create it before hand, or at the time of inserting the
auction).

If, as you say, the first method (triangle) is acceptable, that's good
to know.  However, due to my requirements, I think the second
method is the one I need to go with... don't want someone creating
an auction without a group.

Thanks once again.
Trooper

06-April-2003
==========