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).

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?

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

after9 wrote:
> however, escapes its' explanations and I'm having a hard time
>
> As of now, I have Table B handle its' references to table A using
>

Yo might want to take a look at the newsgroup called:
alt.possessive.its.has.no.apostrophe

"after9" <ggamache@gmail.com> wrote in message
news:3030d7f6-130a-42b8-9234-00b8761a083d@c58g2000hsc.googlegroups.com...
> 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).
>
> 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?

If you would describe your problem in terms of entities and relationships it
would be much easier to help you. For example:

An Employee works on many Projects
A Project is worked on by many Employees

A Project is managed by 1 Employee
An Employee may manage 1 or more Projects

An Employee works on Project as Role

Forget about Objects and Collections.

Evan Keel wrote:
> "after9" <ggamache@gmail.com> wrote in message
> news:3030d7f6-130a-42b8-9234-00b8761a083d@c58g2000hsc.googlegroups.com...
>> 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).
>>
>> 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?
>
> If you would describe your problem in terms of entities and relationships it
> would be much easier to help you. For example:
>
> An Employee works on many Projects
> A Project is worked on by many Employees
>
> A Project is managed by 1 Employee
> An Employee may manage 1 or more Projects
>
> An Employee works on Project as Role
>
> Forget about Objects and Collections.
>
>

Apparently he/she is trying to achieve an OO-to-Relational environment
conversion. While this can be done, it can be extremely difficult to
manage and even more difficult to create/design correctly.

I like the title of this link:
http://www.tonymarston.net/php-mysql...-are-evil.html

A synopsis of trying to combine OO programing style with a relational
database and using that database to store "objects" and "collections"
and "classes".

Michael Austin wrote:
> Evan Keel wrote:
>> "after9" <ggamache@gmail.com> wrote in message
>> news:3030d7f6-130a-42b8-9234-00b8761a083d@c58g2000hsc.googlegroups.com...
>>> 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).
>>>
>>> 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?
>>
>> If you would describe your problem in terms of entities and
>> relationships it
>> would be much easier to help you. For example:
>>
>> An Employee works on many Projects
>> A Project is worked on by many Employees
>>
>> A Project is managed by 1 Employee
>> An Employee may manage 1 or more Projects
>>
>> An Employee works on Project as Role
>>
>> Forget about Objects and Collections.
>>
>>
>
> Apparently he/she is trying to achieve an OO-to-Relational environment
> conversion. While this can be done, it can be extremely difficult to
> manage and even more difficult to create/design correctly.
>
> I like the title of this link:
> http://www.tonymarston.net/php-mysql...-are-evil.html
>
>
> A synopsis of trying to combine OO programing style with a relational
> database and using that database to store "objects" and "collections"
> and "classes".
>

Not at all. This is a simple one-to-many relationship, with the many
being a part of a collection. Paul's response correct.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Sun, 08 Jun 2008 04:09:04 +0200, Jerry Stuckle
<jstucklex@attglobal.net> wrote:
> Michael Austin wrote:
>> Apparently he/she is trying to achieve an OO-to-Relational environment
>> conversion. While this can be done, it can be extremely difficult to
>> manage and even more difficult to create/design correctly.
>> I like the title of this link:
>> http://www.tonymarston.net/php-mysql...-are-evil.html
>> A synopsis of trying to combine OO programing style with a relational
>> database and using that database to store "objects" and "collections"
>> and "classes".
>>
>
> Not at all. This is a simple one-to-many relationship, with the many
> being a part of a collection. Paul's response correct.

Paul's response IS correct. :P

...allthough not helpfull...
--
Rik Wasmus
....spamrun finished

Rik Wasmus wrote:
> On Sun, 08 Jun 2008 04:09:04 +0200, Jerry Stuckle
> <jstucklex@attglobal.net> wrote:
>> Michael Austin wrote:
>>> Apparently he/she is trying to achieve an OO-to-Relational
>>> environment conversion. While this can be done, it can be extremely
>>> difficult to manage and even more difficult to create/design correctly.
>>> I like the title of this link:
>>> http://www.tonymarston.net/php-mysql...-are-evil.html
>>> A synopsis of trying to combine OO programing style with a
>>> relational database and using that database to store "objects" and
>>> "collections" and "classes".
>>>
>>
>> Not at all. This is a simple one-to-many relationship, with the many
>> being a part of a collection. Paul's response correct.
>
> Paul's response IS correct. :P
>
> ..allthough not helpfull...

Oops - you're right. In editing I deleted too much (was too wordy).

But it should have been eminently helpful.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

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.