multiple parent categories

[Howard]

how would you design a table that allows an item to belong to multiple
parent categories.

single parent example:
select * from table1 where parentid = 5
in this case parentid is an indexable unique integer

possible multiple parent:
select * from table1 where parentid = '5,6'
a problem is that i don't know the order of the ids it could be '6,5'
how do you design a database that does this?

Thanks,

Howard

[Trey Walpole]

Re: multiple parent categories

use a linking table for a many-many relationship

e.g., [minimal schema]

create table PARENT (
ParentID int not null unique
)

create table CHILD (
ChildID int not null unique
)

create table PARENT_CHILDREN (
ParentID int not null references PARENT (ParentID),
ChildID int not null references CHILD (ChildID),
primary key (parentid, childid)
)


Howard wrote:[color=blue]
> how would you design a table that allows an item to belong to multiple
> parent categories.
>
> single parent example:
> select * from table1 where parentid = 5
> in this case parentid is an indexable unique integer
>
> possible multiple parent:
> select * from table1 where parentid = '5,6'
> a problem is that i don't know the order of the ids it could be '6,5'[/color]

The problem with this is that this is not even first normal form. Never,
ever, do this.
[color=blue]
> how do you design a database that does this?
>
> Thanks,
>
> Howard
>
>[/color]

[Lasse Vågsæther Karlsen]

Re: multiple parent categories

Howard wrote:[color=blue]
> how would you design a table that allows an item to belong to multiple
> parent categories.
>
> single parent example:
> select * from table1 where parentid = 5
> in this case parentid is an indexable unique integer
>
> possible multiple parent:
> select * from table1 where parentid = '5,6'
> a problem is that i don't know the order of the ids it could be '6,5'
> how do you design a database that does this?
>
> Thanks,
>
> Howard
>
>[/color]

Standard database modelling theories state that:

- in a one-to-many relation, you copy the identifier (primary key) from
the one side to the many side. Example: One person got many phone
numbers, so you copy the person id into the phone number object.
- in a many-to-many relation (I assume each category can have many items
too), you need a link table in between. The format of the link table
should be that it contains the identifiers of the two objects it sits
between. In your case the link table would hold the parentid, and the itemid

All these columns mentioned above should be foreign keys to the
respective tables as well, when you get to the database part of it.

--
Lasse Vågsæther Karlsen

http://usinglvkblog.blogspot.com/

mailto:lasse@vk arlsen.no
PGP KeyID: 0x2A42A1C2