On Fri, 06 Jun 2008 22:13:21 +0200, after9 <ggamache@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