database model structure

Question

I have a column groups. Groups has different type stored in group_types (buyers, sellers, referee). Only when the group is of type buyer it has another type (more specialized) like electrical and mechanical.

I'm a bit puzzled with how I will store this in a database.

Someone can suggest me a database structure?

thanks

Quassnoi · Accepted Answer · 2009-05-27T15:33:26.800

Store your group_types as a hieararchical table (with nested sets or parent-child model):

Parent-child:

typeid parent name

1      0      Buyers
2      0      Sellers
3      0      Referee
4      1      Electrical
5      1      Mechanic

SELECT  *
FROM    mytable
WHERE   group IN
        (
        SELECT  typeid
        FROM    group_types
        START WITH
                typeid = 1
        CONNECT BY
                parent = PRIOR typeid
        )

will select all buyers in Oracle.

Nested sets:

typeid lower  upper  Name
1      1      2      Buyers
2      3      3      Sellers
3      4      4      Referee
4      1      1      Electrical
5      2      2      Mechanic

SELECT  *
FROM    group_types
JOIN    mytable
ON      group BETWEEN lower AND upper
WHERE   typeid = 1

will select all buyers in any database.

Nested sets is implementable anywhere and more performant, if you don't need hierarchical ordering or frequent updates on group_types.

Parent-child is implementable easily in Oracle and SQL Server and with a little effort in MySQL. It allow easy structure changing and hierarchical ordering.

See this article in my blog on how to implement it in MySQL:

Hierarchical queries in MySQL

score 0 · Answer 2 · answered May 27 '09 at 15:24

0

You could possibly store additional types like, buyer_mechanical or buyer_electrical.

answered May 27 '09 at 15:24

J. Polfer

12,251
10
54
83

score 0 · Answer 3 · answered May 27 '09 at 15:27

You could try:

Group
group_id
group_name
group_parent_id

with entries (1, buyers, 0), (2, sellers, 0), (3, referee, 0), (4, electrical, 1), (5, mechanical, 1)

This has the advantage of being infinitely scalable, so each subgroup can have as many subgroups as you want.

score 0 · Answer 4 · answered May 27 '09 at 15:28

Typically, you have extension tables. These are simply additional tables in your schema which hold additional information linked to the main table by some type of key

For example let's say your main table is:

People
  PersonId int, PK
  GroupTypeId int, FK to GroupTypes
  Name varchar(100)

GroupTypes
  GroupTypeId int, PK
  GroupTypeName varchar(20)

BuyerTypes
  BuyerTypeId int, PK
  BuyerTypeName varchar(20)

BuyerData
  PersonId int, FK
  BuyerTypeId int FK

==== Additionally, the BuyerData would have a composite primary key (PK) on PersonId and BuyerTypeId

When pulling Buyer data out, you could use a query like

SELECT *
  FROM People P 
    INNER JOIN BuyerData BD on (P.PersonId = BD.PersonId)
    INNER JOIN BuyerTypes BT on (BD.BuyerTypeId = BT.BuyerTypeId)

score 0 · Answer 5 · answered May 27 '09 at 15:36

grouptype: ID, Name ('buyers', 'sellers', 'referee')

group: GroupTypeID, ID, Name ('electrical' and 'mechanical' if grouptypeid == 'buyers')

contact: GroupTypeID (NOT NULL), GroupID (NULL), other attributes

Table Group is populated with records for GroupTypes as required.

Contact.GroupID can be NULL since a GroupType need not have any Groups.

UI has to take care of Group selection. You can have a trigger check the group/type logic.

database model structure

5 Answers5

Linked