I've got a column groups. Groups has different type saved in group_types (purchasers, retailers, referee). Only if the audience is of type buyer it's another type (more specialized) like electrical and mechanical.

I am a bit puzzled with the way i will store this inside a database.

Someone can suggest us a database structure?

thanks

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

Parent-child:


typeid parent title

1            Purchasers

2            Retailers

3            Referee

4      1      Electrical

5      1      Auto technician

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

will choose all purchasers in Oracle.

Nested sets:


typeid lower  upper  Title

1      1      2      Purchasers

2      3      3      Retailers

3      4      4      Referee

4      1      1      Electrical

5      2      2      Auto technician

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

will choose all purchasers in almost any database.

Nested sets is implementable anywhere and much more performant, if you do not need hierarchical ordering or frequent updates on group_types.

Parent-child is implementable easily in Oracle and SQL Server and after some effort in MySQL. It allow easy structure altering and hierarchical ordering.

Check this out article during my blog regarding how to implement it in MySQL:

You may store additional types like, MySQL or buyer_mechanical.

You could attempt:

buyer_electrical

It has the benefit of being infinitely scalable, so each subgroup might have as numerous subgroups as you would like.

Typically, you've extension tables. They are simply additional tables inside your schema which hold more information from the primary table by some form of key

For instance let us say your primary table is:

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)

==== Furthermore, the BuyerData might have an amalgamated primary key (PK) on PersonId and BuyerTypeId

When tugging Buyer data out, you could utilize a question like

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

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

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

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

Table Group is populated with records for GroupTypes as needed.

Contact.GroupID could be NULL since a GroupType do not need to have Groups.

UI needs to take proper care of Group selection. You'll have a trigger look into the group/type logic.