[thelist] Database Schema

Ken Moore psm2713 at hotmail.com
Sun May 28 18:27:42 CDT 2006


Hi all,

Hershel Robinson ask about two schemas.

For a database designer, there is not even a discussion. The third normal 
form (TNF) states that there can be no repeating data. For example, if you 
want to track employee’s kids, you do not have fields named kid1, kid2, … 
kidn. You would have a table named kid_table as you described:

id
parent_id (foreign key)
name
bdate

There are lots of reasons. How many kids do make room for? It wastes space. 
Etc. But most reasons have to do with getting the data out. One fairly 
simple query returns all of the data and avoids things like:
“if kid_table.parent_id = parent_table.id and kid_table.name <> ‘’
then do_something”

Repeat that for up to some number of kids and you see the problem. And, what 
happens if someone has more kids than you allow for?

Consider the option:
“select * from kid_table
where emp_table.id = kid_table.partent_id”

This query returns all of the kids for that parent. You can put this query 
into a loop and get all kids for all parents, as here, or for a single 
parent using a variable such as:
“select * from kid_table
where var_this_parent_id = kid_table.partent_id”

I would suggest that you Google for a short tutorial on Third Normal Form 
and read a very basic book on SQL. Between the two, you should have a solid 
foundation to build on.

Ken
HTH

>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/
>--
>
>* * Please support the community that supports you.  * *
>http://evolt.org/help_support_evolt/
>
>For unsubscribe and other options, including the Tip Harvester
>and archives of thelist go to: http://lists.evolt.org
>Workers of the Web, evolt !

_________________________________________________________________
Don’t just search. Find. Check out the new MSN Search! 
http://search.msn.click-url.com/go/onm00200636ave/direct/01/




More information about the thelist mailing list