[thelist] Database Schema

Ken Moore psm2713 at hotmail.com
Mon May 29 09:16:02 CDT 2006


Hi all,

Mattias Thorslund wrote: "There are no absolutes"

Agreed. For example, in employee_table, "spouse" is generally a single 
field. Technically, this is a violation of 3NF because it overwrites the 
fact that 10 years ago Boris, who works at the CIA and is now married to 
Mary, was married to Natasha, who was a Russian spy. Nothing is absolute but 
3NF is the best place to start from.

Ken

>
>Ken,
>
>Well, there are cases where experienced database designers intentionally
>ignore 3NF. Logging could very well be such a case, since it happens
>often, and must be fast in order to get out of the way quickly, leaving
>some resources for what the application is actually meant to do. Doing
>3-5 inserts rather than 1 for each log event could mean a noticeable
>performance hit. Or not, depending on the amount of traffic, the
>hardware and other things.
>
>Atomicity is probably not essential to logging, but if it were, there
>would also be a need to wrap the inserts into a transaction, adding yet
>some more overhead.
>
>Another case when foregoing 3NF is often done is reporting:
>Pre-populating a semi-temporary table with calculated data can allow for
>a much quicker loading report, rather than making the same expensive
>calculations/summarizations, etc happen every time.
>
>So, there are no absolutes. The best choice depends on the requirements.
>
>Mattias
>
>
>
>Ken Moore wrote:
> > 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/
> >
>
>--
>
>* * 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 !

_________________________________________________________________
FREE pop-up blocking with the new MSN Toolbar – get it now! 
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/




More information about the thelist mailing list