About 

Scenario:  We want to be able to arbitrarily add fields to any inventory object.  For example, the inventory object Person comes with the fields Name and Phone by default.  A user decides they would like to add an Email field to the information tracked by Person.

Problem:   The problem here is coming up with a good database schema to organize these extra fields.  Should we add extra columns to the object table?  What if you want multiple email addresses per object?  Our first round solution was to add 2 new tables (data & mapping tables) for each new field, but this would quickly bloat the DB with 2 new tables for each field. 

Solution:  Our solution is to create a central mapping table for all these added fields, this way we only add one new DB table for each field type.

Schema:  

There are four tables to explain with this new approach:  raiv_map, types, modules, and the module's data table.   These tables are described below.

Modules -  used to track modules installed into RAIV

ID	Mod_name	Enabled	Parent_id

RAIV_Map -  used to map relationships between modules, their data tables, and their type

ID	Par_id	Obj_id	par_type_id	obj_type_id

Types - used to track the type names of installed modules

ID	Name	Mod_id

Person_data - data table for Person module

ID	Name

How it works

It starts when a module is installed.  When we install the module Person, it modifies two tables:  Modules and Person_data is created.

First Person is added to the Modules table.  ID is an auto-incremented value, Mod_name and Enabled are self-explanatory, and Parent_id is 0 because Person has no parent module(explained later)

Modules

ID	Mod_name	Enabled	Parent_id
1	Person	1	0

And Person's data table is created.  Lets say we create a new person with the name John.  The data table would look like

Person_Data

ID	Name
1	John

Nothing special happens here.  This is just basic tracking of modules and data.  But what if we want to track a person's email also?  Or address?  What if they have multiple emails?  This is where RAIV_Map comes in. 

Lets create a simple Person_email module, that will be a sub module of Person.  We install it.  Parent_id will be 1, because its parent module Person has ID = 1

Modules

ID	Mod_name	Enabled	Parent_id
1	Person	1	0
2	Person_email	1	1

Next, since Person and Person_email will be using the RAIV_Map table, they will need to register a type in the Types table.  It should be noted, a module may choose to register multiple types and this is ok.

Types

ID	Type_name	Mod_id
1	Person	1
2	Person_email	2

 And we will need a data table...

Person_Email_Data

ID	Email

Now we add a new email for John.  We add to our data table...

Person_Email_Data

ID	Email
1	foo@bar.com

 ...and we add an entry to RAIV_Map.  Object_id is the object (i.e. person in this case) this data relates too.  So John's ID is 1.  Item_id is the ID of the email entry, so 1.

(the italics are for explanation, they are not DB entries)

Raiv_Map

ID	Par_id	Obj_id	par_type_id	obj_type_id
1	1  (John)	1  (foo@bar.com)	1   (Person)	2   (Person_Email)

 This allows flexible tracking of these add-on fields.  So we can easily add another email for John... say blah@foo.com

Add to the data table...

Person_Email_Data

ID	Email
1	foo@bar.com
2	blah@foo.com

 And map it back to John using RAIV_Map...  

(the italics are for explanation, they are not DB entries)

Raiv_Map

ID	Par_id	Obj_id	par_type_id	obj_type_id
1	1  (John)	1   (foo@bar.com)	1  (Person)	2  (Person_Email)
2	1  (John)	2   (blah@foo.com)	1  (Person)	2  Person_Email)