multiple parent categories

  • Thread starter Thread starter Howard
  • Start date Start date
H

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

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'
Click to expand...

The problem with this is that this is not even first normal form. Never,
ever, do this.
 
Howard said:
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
Click to expand...

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.
 
Back
Top