[thelist] Database Schema
Hershel Robinson
hershel at galleryrobinson.com
Sun May 28 13:00:06 CDT 2006
I am designing a large database schema for a web app. The app manages
the business activities of a direct marketing web retailer. The issue in
question is how to store a log of all activities that occur in the
system. Actions we store include:
- client logs in to the system
- client adds a product to his cart
- client edits the details of product
- client logs out
- a Sales Agent sends an email
- a Sales Agent sends a fax
- a Sales Agent makes a phone call to a prospective client
- a Sales Manager assigns a prospective client to a certain Sales Agent
There are many more actions, some of which are unique to the particular
business in question. The question is which of the these two approaches
for the DB table structure:
APPROACH ONE:
The activity table has fields:
id
action_id (foreign key to actions table)
user_id (foreign key to users table)
date_time
There is then a table called activity_fields with fields:
id
activity_id (foreign key to activity table)
field_name
field_value
The actual activity (a login or phone call) is a record in the activity
table. Each datum which needs to be stored with that action will be
stored in the activity_fields table. Thus the phone number which was
called or the email address to which an email was sent or the product id
and quantity of a product added to a cart will be stored as field_name
field_value pairs--each datum becoming one record in the activity_fields
table.
APPROACH TWO:
The activity table has fields:
id
action_id (foreign key to actions table)
user_id (foreign key to users table)
date_time
field_1
field_2
field_3
The actual activity (login or phone call) is again a record in the
activity table, but now it has with it as well as any relevant
details--each one being stored in one of the generic fields, field_1,
field_2, etc. The mapping of what each field means for each action is
stored in the actions table. So for a phone call, the phone number would
be in field_1, for an email the email in field_1, for a client adding a
product to a cart, the product id would be in field_1 and the amount
added in field_2. Should we find that we need more fields, we can always
add more in the future.
Those are the two approaches we have come up with.
I have my personal opinion on this subject, but I would like to hear
input from Evolters, if anyone is interested. :)
Thanks,
Hershel
--
Gallery Robinson Web Services
http://web.galleryrobinson.com/
More information about the thelist
mailing list