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?
group_types like a hieararchical table (with
nested sets or
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
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
Parent-child is implementable easily in
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
You may store additional types like,
You could attempt:
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.