I'm working on a fairly simple contact database system with the exception that on a per installation basis, the system needs to handle "extra fields" created by the user without too much fuss or changes to the code for other installations of the system.
My working model so far is:
Primary Table
=============
contactid: int
+ a handful of basic fields: username, password, email, etc, mostly all varchar
Custom Fields Table
=============
fieldid: int
fieldtype: varchar (eg. text, dropdown, radiobox)
name: varchar (eg. Cell Phone, Member Status)
values: varchar (eg. if type is a dropdown, lists available options comma seperated: i.e. active,inactive,pending. otherwise, not really used)
Custom Storage
=============
contactid: int (to primary table)
fieldid: int (to custom field table)
value: varchar
The idea is that there would be no limit to the number of fields that could be custom added and each field would get it's own row for each contactid in the table. I realize this table would grow quite large as contacts & custom fields increased. I guess the other option would be to have the Custom Storage table look like:
Custom Storage
=============
contactid : int (to primary)
field1: varchar
field2: varchar
field3: varchar
field4: varchar
field5: varchar
etc.
And have a limited number of custom fields available and each field would just be stored in the appropriate column with only one record in the Custom Storage table per contact. But this would limit the number of custom fields that each installation could make use of (not ideal).
Maybe there is a third option that I'm missing here, or there's an error in my logic.
Also, if my logic is sound for the first proposal, then this is the SQL statement that i would be using to select a contact from the master table and then all the custom fields from the custom storage table:
SELECT
t1.*,
t2.value AS field1,
t3.value AS field2,
t4.value AS field3,
etc,
FROM `contacts` AS t1
LEFT JOIN `custom_storage` AS t2 ON t1.contactid = t2.contactid
LEFT JOIN `custom_storage` AS t3 ON t1.contactid = t3.contactid
LEFT JOIN `custom_storage` AS t4 ON t1.contactid = t4.contactid
etc,
WHERE
t1.contactid = 3 AND
t2.fieldid = 1 AND
t3.fieldid = 2 AND
t4.fieldid = 3;
Which seems a little ridiculous, but then again it works, albeit, not tested on a large table. I just can't imagine that my problem or a solution for it is unique, so there must be either a methodology out there or a type of SQL JOIN that I'm missing because as it stands now, I can't wrap my head around solving this problem without an overly complex series of unwieldy SQL statements.
Start Free Trial
