Dear Sirs,

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

What it´s better?

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

I have a system in Access 97, I´m 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, I´ll would like your advise on it.

Thanks a lot.

Best Regards,


Delcio Torres

three tables is better

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.

more tables are easier to be administrated.
BUT sometimes business logic and frequency of using makes one table data faster ... sometimes.

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

Thank you all for your answers!

What I´m 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.

We´ll 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?

I´ve 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

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.

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

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.