Results 1 to 9 of 9

Thread: Design Doubt

  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Talking Design Doubt

    Dear Sirs,

    I have a design question, I would like your help. This is my first time in a forum, please forgive me.

    What its better?

    A table with 180 fields or three tables with some kind of relationship?

    I have a system in Access 97, Im planning to move to Interbase 7 to adapt to company standards. Now the project will be the standard for the company and once it will have many, many records, Ill would like your advise on it.

    Thanks a lot.

    Best Regards,


    Delcio Torres

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    three tables is better
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Posts
    87
    I tend to agree with r937 only because it is highly doubtful a table with 180 columns is normalized. However, without more info about the nature of the data and the business rules, its impossible to say.
    Oracle - DB2 - MS Access -

  4. #4
    Join Date
    Oct 2003
    Location
    Sofia
    Posts
    16
    more tables are easier to be administrated.
    BUT sometimes business logic and frequency of using makes one table data faster ... sometimes.

  5. #5
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    Even if you have the computer resources (memory, etc.) to handle a 180 field table, it seems too big to be practical -- too big for a person to keep track of. There is no hard and fast rule, but probably 20 - 40 fields are about as wide as someone can pracically deal with along.

    I would suggest breaking it down to as many tables as possible. Chances are it can be broken down to at least 10 or even 20 tables.

    The following link may be helpful. There is a good example and if you can follow along with the example of a dog-trainer teaching different tricks to different puppies it may give you ideas for how to break down your tables:


    http://www.gslis.utexas.edu/~l384k11w/normover.html

  6. #6
    Join Date
    Oct 2003
    Posts
    2

    Design Doubt II

    Thank you all for your answers!

    What Im doing is a time track system to be crossed with the entrance access. Like this:

    EMPLOYEE->CONTROL

    CONTROL
    PKControl
    FKEmployee
    DateIn
    DateOut
    HourIn
    HourOut
    HourType

    So for each line of CONTROL it will have an associated Employee.

    Imagine for 01(one) employee in one month it will have at least 30 registers. In one year 360 registers. We have around 200 employees, so this would grow-up to 72000 registers per year or 6000 per month.

    Well have queries for checking monthly hour report, period report, department report, etc.

    So my doubt was, should I have one big table CONTROL, with the registers of month, employee(fk), department(fk), and DateIn(1)...to...DateIn(31), DateOut(1)...to...DateOut(31), HourIn(1)...to...HourOut(31), HourType(1)...to...HourType(31), about 180 fields per register or one table CONTROL with each register containing DateIn, DateOut, HourIn, HourOut, HourType?

    Ive thougth of the first option maybe to speed-up the queries, once again it will be my first time with a REAL database like Interbase.

    Best Regards,

    Delcio

  7. #7
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    Why not a simple table:

    MasterLog
    Index, EmployeeID, DoorID, DateTimeStamp, ActivityType


    Doors
    1, FrontDoor
    2, BackDoor
    3, EastDoor
    4, VaultDoor

    Employees
    1, Joe Smith
    2, Mary Jones
    etc.

    ActivityType
    1, Entrance
    2, Exit
    (optionally more complex)
    EntranceWithCard
    EntrancePINonKeypad
    EntrancebyVoiceRecognition
    EntrancebyFingerprintID
    ExitWithCard
    ExitPINOnKeypad
    ExitbyFireEscape
    (long field names given just to be descriptive)

    This could be made more complex by having entrance types set up for high vs low-security areas or adding a security level (low/medium/high/etc) to each door and or each employee


    By using the Long DAteTime in Access you can store the date and time down to the secon in one field. You can then manipulate this field to pull ou the date, time, hour, minute etc. as needed. or even to separate them into different fields in your roll-up tables.

    Then from this MasterLog you can set up queries/macros/code to make roll-up tables for whatever reports you need - say the total time each employee was in the building each day or week, or the number of entrances etc. Or maybe you need to know the % of each employees shift that was spent in a high security area

    You can do this from queries, but as the table can get so large if you run automated queries to make roll-up tables each day/week/month you will be able to ahve summary data in an easy format to base reports on.

  8. #8
    Join Date
    Nov 2003
    Location
    Madison, WI
    Posts
    2

    Re: Design Doubt II

    Originally posted by delcio

    So my doubt was, should I have one big table CONTROL, with the registers of month, employee(fk), department(fk), and DateIn(1)...to...DateIn(31), DateOut(1)...to...DateOut(31), HourIn(1)...to...HourOut(31), HourType(1)...to...HourType(31), about 180 fields per register or one table CONTROL with each register containing DateIn, DateOut, HourIn, HourOut, HourType?

    mdr02125's design is the way to go since it is in third normal form (3NF). It minimizes storage requirements and avoids anomalies.

    The use of repeating groups in the proposed CONTROL table means that the table isn't even in first normal form (1NF). Maintaining reports written off of the CONTROL table would be a headache and the repeating groups make data anomalies inevitable.

    --
    TCHM

  9. #9
    Join Date
    Oct 2003
    Posts
    706

    Exclamation

    Yes, this is the approach to use. You see, what you are recording in this database is two or maybe three "things":

    * Employees who have access
    * Keycards they use
    * A history of their entry and exits.

    The history table contains one row for each time someone goes in and one for each time someone goes out, with all of the particulars (time, card-ID, etc) of that one event.

    For reporting you would then do what's called a "crosstab," where you have a row for each employee and a column for each time/date and in each cell you have some summary, e.g. number of entries or exits on that date. Most reporting tools and many query systems can do that. Your "108 column table" is more-or-less such a crosstab, but my point is that you don't =store= the data that way initially. Instead, you =build= the crosstab result on the fly in the process of preparing your report.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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