Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011

    Unanswered: Database Normalization Question

    It has been awhile since I have normalized a database with this many levels and I'm wanting to just get some opionons and facts on if I'm doing this the right way.

    The senario I'm giving is a small portion of my database, but if I understand this part, the rest should easily come into their place.

    I apologize for not attaching a visio or other mapping attachment, but currentlly limited to word and the black board in my office (which is where I do most of my diagraming)

    [Event] -> [Sub Event] -> [Action] -> [Sub Action] -> [Log] -> [Individuals]

    That is a very streamlined look at one stand of the database.

    The basic description:
    An EVENT occurs, within an alotted time frame, multiple Sub-Events can occur (These events are semi-seperate in nature, but must link to the main event). A Sub-Event can spawn multiple actions. Each action has Sub-Actions which must be logged by the minute it starts to the minute it ends. Each Log can have multiple Individuals associated with it.

    Psuedo Real World Example

    Baseball Game -> Inning -> Offence -> Batter -> Swing -> Team Member

    Not the best example but it gets the point across. So the way i'm looking at this each section must use the previous sections Primary key plus its own primary key. So [Individuals] will have 6 primary keys plus the information that I want to collect.

    First time I've posted a database normalization question. I apologize if I've described this poorly or anything else I have done wrong. Thanks for any help that is given!

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    Er, no.

    If you try to record the game's ID against the team member (in this example), you are not following the normal forms. Each level only needs to have a foreign key pointing to the level above. That way, you can chain the tables together in a query to find out the Event that owns an Individual.

    EventID (PK)
    [Other fields]

    SubEventID (PK)
    EventID (FK)
    [Other fields]

    ActionID (PK)
    SubActionID (FK)
    [Other fields]


    , tblEvents.EventName
    tblEvents INNER JOIN tblSubEvents
    ON tblEvent.EventID = tblSubEvent.EventID
    INNER JOIN tblActions
    ON tblSubEvent.SubEventID = tblAction.SubEventID
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    or have a single table with an optional foreign key pointing to a parent event
    if you also have a foreign key to and event types table then you can identify what the types are.
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2011
    I appreciate your answers. Sorry for belated response, I have been away traveling. It makes more sense to only have to attach a foreign key 1 step above. I must have been smoking something that was pretty obvious. Looks like I'll be doing some restructing of my DB.


    Thanks once again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts