Hi,
I'm currently desiging something of a membership database. There are a couple of rather large design problems. I want to focus on one at the moment.
There are people, and there are organisations. A person may be a member of an organisation, or they may be an entity on their own. They can belong to multiple organisations.
I'm going to have a universal contacts table, and there will be two (at least) types - Person, and Organisation.
The problem is, that the information that the users will want to store for a contact entry will vary and grow. Eg, tomorrow they may say "we also want to record the marital status of the person". Two weeks later, "we want to know the style of haircut each person has". So, I need to modify the database, add those fields to the tables, then update the php to allow for viewing, editing and adding these new fields.
Is there a better way? I'm considering an attributes table, where users can define their own new, simple attributes. This could get long though, because it's essentially a pseudo database inside an actual database. I'm sure this problem must have been crossed before.
Do you just say "I have to make the changes"? Or have you thought of good ways to implement this so that users have some control over what attributes are stored related to each entry?
Contacts with new attributes
Moderator: General Moderators
i would probably have the following tables:
person (person_id, ...)
person-organisation (person_id, organisation_id)
organisation (organisation_id)
and then i would create 3 tableclasses with the correct columns/attributes and rules...
and in my view component i would define wich and how the attributes need to be displayed..
if an attribute is added, i need to edit the class and the views (i case i dont have a default option to display all attributes) that display this table.
person (person_id, ...)
person-organisation (person_id, organisation_id)
organisation (organisation_id)
and then i would create 3 tableclasses with the correct columns/attributes and rules...
and in my view component i would define wich and how the attributes need to be displayed..
if an attribute is added, i need to edit the class and the views (i case i dont have a default option to display all attributes) that display this table.
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
My be overkill but due to the nature of attributes, (they could be any "type" such as text, integer, boolean etc) I would actually add two, probably three tables
userinfo (user_id integer, attr_obj text)
attributes (attribute_id, attr_name, attr_desc, attr_type, attr_default);
(probably needed) userinfo2attributes (user_ref,attribute_ref)
If you are then have a "class" to handle the attributes (if you are using Objects) then the attr_obj field in the userinfo table would contain a serialised version of the class containing all the attribute details. The class could also contain all methods for things like validation etc.
userinfo (user_id integer, attr_obj text)
attributes (attribute_id, attr_name, attr_desc, attr_type, attr_default);
(probably needed) userinfo2attributes (user_ref,attribute_ref)
If you are then have a "class" to handle the attributes (if you are using Objects) then the attr_obj field in the userinfo table would contain a serialised version of the class containing all the attribute details. The class could also contain all methods for things like validation etc.
A DataMapper solution will isolate the php code from the database schema -see PoEEA catalog for a brief description. You'll need to buy the book for a fuller description of the pattern and object-relational mapping in general. It's the most complex of the various options so take a deep breath before diving in.