On Jun 6, 5:03 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Fri, 06 Jun 2008 22:13:21 +0200, after9 <ggama...@gmail.com> wrote:
> > Database normalization has answered many of the questions I've had
> > regarding proper database structure. One scenario I'm running into,
> > however, escapes its' explanations and I'm having a hard time
> > quantifying it into a phrase with which to search google.
>
> > I have Table A which defines an object. It has a primary key that
> > autoincrements each time an object is created as well as a few other
> > columns that describe said object.
>
> > Table B contains, as well as other things, a collection of items from
> > Table A. However, and here is the tricky part, I want it to be
> > 'dynamic.' Dynamic in the sense that the user may want Table B to
> > only use 1 of table A's object but they may also want more (up to a
> > seemingly arbitrary call on my part - 3).
>
> Do you have real reason to limit them?
>
> > As of now, I have Table B handle its' references to table A using
> > foreign keys. In the instance a user does not want to use the max
> > allotted(3) the column is filled with a null value:
>
> > TABLE B
> > object_1 | object_2 | object_3 |
> > foreign_key foreign_key null
>
> > The problem with this, though, is inserting and removing objects into
> > and from Table B can be tricky. I'm using php to communicate with my
> > database, and I've got some functions to handle it alright, but I
> > believe there has got to be a better solution. Thoughts?
>
> Table A
> id, etc...
>
> Table Collections
> id, etc...
>
> Table CollectionA's
> collection_id, a_id
>
> For instance:
> Table A
> 1 object1
> 2 object2
> 3 object3
> 4 object4
> 5 object5
>
> Table Collections
> 1 Coll1
> 2 Coll2
>
> Table Collection_As
> 1 1
> 1 2
> 1 4
> 2 3
> 2 5
>
> Con:
> - an empty collection can exist (no references to A in Collection_As),
> allthough easily deleteable if you wish with one query. And in the current
> scheme is the same drawback.
>
> Pro:
> - a removed object is automatically removed from a collection on deletion
> - no need to know about other A's when adding to, or removing an A from a
> Collection
> - A's can be in more then one Collection
> - no limit on the number of A's in an Collection
> - easier to query when joining to possible outside tables (1 join vs. 3)
> ... and several more...
> --
> Rik Wasmus
> ...spamrun finished

Rik it looks like I'll be going with your solution. Thanks.

Michael, thank you for the link. I'll read through it and see if it
can help me understand my problem better.

Evan: how about this.

A child owns many toys.
A child's toy chest may hold one or more toys.

A toy chest is owned by one child.
A child may not own more than one toy chest.

Even simpler (and using my OP as a reference), the child's toys are
Table A and the toy chest is Table B.