Re: [cgiapp] Before I go off into a codestorm... [Mini-CRM Question]

[Joel Gwynn <suppressed>]

Since nobody else has jumped in ... What I've done is added two
tables: attributes, and attribute_values.

ATTRIBUTES -- looks something like this:
attribute_id  int(11)
object_name   varchar(50) -- generally corresponds to a table, like users
attribute_name varchar(50)
created_dtm datetime
updated_dtm    datetime
active_ind      char(1)
field_type   varchar(10) -- int|float|varchar ... whatever
required       char(1)
form_order      int(11)
attribute_type  enum('single','multi')
-- The last four are for form display and validation.

ATTRIBUTE_VALUES
attribute_id
object_id
created_dtm
value
active_ind
updated_dtm

So (in theory), I can attach arbitrary attributes to any table in my
database, and have a generic getObjectAttribute() function to retrieve
attributes for a given user, or what-have-you.

It gets a little tricky with the attribute_enums, which I've been
storing in another table.

But on the whole, this makes it easy, or at least manageable, to build
a form that an end-user can use to add and manage attributes.

HTH
Joel

On 5/19/05, Jason Purdy <suppressed> wrote:
> I wanted to survey the great crowd here & see if such a thing already
> exists before I launch into some code mayhem.
> 
> I've built a few subscription databases with fields like name, address,
> zip, opt-in preferences, etc.
> 
> Now my boss wants more "metadata" about people, like boolean flags
> (affiliate, open shop), text fields and I suppose datestamps, too.
> He'll also want searching capabilities, etc.
> 
> So instead of widening the "people" table to support these fields (and
> having to alter the table every time), I'm thinking of developing a
> datapoint type of system, where you'd have one table of datapoints:
> 
> Table datapoints:
>   - id
>   - label     (text label)
>   - data_type (bool, text, int, timestamp)
> 
> Then several join tables, based on datatypes:
> 
> Table text_datapoints
>   - datapoint_id  (foreign key, from the "datapoints" table)
>   - people_id    (foreign key, from the "people" table)
>   - value         (text value)
> 
> Table bool_datapoints
>   - datapoint_id  (foreign key, from the "datapoints" table)
>   - people_id    (foreign key, from the "people" table)
>   - value         (bool value)
> 
> [etc...]
> 
> Ok, so after all the database stuff, I'd have to build web interfaces to
> allow the boss to manage the datapoints and also have a "power search"
> (getting some inspiration from Thunderbird's "Search Messages"
> functionality).
> 
> Soooo ... has this type of thing been done before and/or already
> available?  It's kinda like "CRM", but not as complex as something
> Siebel would sell me. ;)  I would envision having this package in a
> bundle that folks could bind to a database table.
> 
> Thanks!
> 
> Jason
> 
> ---------------------------------------------------------------------
> Web Archive:  http://www.mail-archive.com/suppressed/
>               http://marc.theaimsgroup.com/?l=cgiapp&r=1&w=2
> To unsubscribe, e-mail: suppressed
> For additional commands, e-mail: suppressed
> 
>

---------------------------------------------------------------------
Web Archive:  http://www.mail-archive.com/suppressed/
              http://marc.theaimsgroup.com/?l=cgiapp&r=1&w=2
To unsubscribe, e-mail: suppressed
For additional commands, e-mail: suppressed