I have a requirement to create a database where in addition to the fixed schema which I can anticipate and design now the client wants to be able to add arbitrary additional fields. So for example while I can anticipate that they may want a schema which is;
id_site INT AUTOINC,
sitename VARCHAR(64),
address VARCHAR(255),
id_company INT // FK to company table
etc.
They may at some point in future wish to add
accesscode VARCHAR(64)
contactnum VARCHAR(32)
need_photo_id_to_access BIT
etc.
fields which _could_ be common to all sites but which they can not yet define.
Now at the moment I'm looking for design hints for the schema but everything I find on t'interweb points me to EAV designs and tells me I shouldn't do it.
I do not want an EAV design because I can see that would be problematic, The design I have in mind would be something along the lines of;
coretable
id_core INT AUTOINC
<field fieldtype ...>
core-UDF-defs
id-core_udf,
displayname VARCHAR(64)
fldtype INT (some kind of lookup not decided that yet)
<additional data subject to fldtype>
core-udf-values
id_core
fld_<id-core-udf> <type according to core-UDF-defs.fldtype> // value for this field
...
I'm quite happy to extend the schema on the fly so an administrator (not DBA)
might enter the admin part of the system and add the 'accesscode' udf which in the background would run
INSERT INTO core-UDF-defs (displayname, fldtype) VALUES ('accesscode', 1)
and then (assuming for the sake of example that the new field is given the id 37)
ALTER TABLE core-UDF-values ADD COLUMN fld37 VARCHAR(64) NULL
I would then use PHP to spin through the UDFs and display or edit them as required.
Now as I said I couldn't find much about doing this so I've to do this independently so far. The questions are ... is this a good approach? or am I blowing out of my behookey? Can anyone point me to some references that might give me some recommended approaches?
TIA