MySQL Forums :: Database Design & Data Modelling :: User defined fields in a schema


Advanced Search

User defined fields in a schema
Posted by: J Brand ()
Date: July 28, 2011 04:22AM

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

Options: ReplyQuote


Subject Written By Posted
User defined fields in a schema J Brand 07/28/2011 04:22AM
Re: User defined fields in a schema Rick James 07/29/2011 10:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.