Desgin tables for records with over 60 fields

[KHLan]

Hi, there,

I have a simple question to ask for your advice. If a
record contains 60 field that I am interested, for
example, the machine have 60 buttons and I want to know
exactly the time and the buttons that used at 5 min
interval for all the machine (over 1000) every day, should
I design one big table to have

MachineID TimeStamp Button1 Button 2 ...60

Or to split the table into 2 or 3 smaller tables, say
Button 1 to 20 at one table, Button 21-40 at one table and
Button 41-60 at one table with Machine ID and TimeStamp
repeated

What is the pros and cons for that? If split, how can I
link the record back together? I know a "foolish?" one to
build a query to group the MachineID and TimeStamp field
and use the query to link the tables together. However,
it takes a long time for the query to run, is there other
smart way?

Thank you.

KHLan

 

[Douglas J. Steele]

The correct approach is to have a table that has the following fields:

MachineID TimeStamp ButtonNumber ButtonValue

and have 60 rows for each timestamp.

Anytime you have field names like Button1, Button2, etc., that's a sure sign
of a repeating group, which violates database normalization principle.

 

[KHLan]

Thanks Doug,

As we use a probe to download the data from the machine
directly, as a result, it is easier to set up these 60
fields instead of messaging the data. In fact the table
has been set up (so fields are divided into two tables)
when I start the work; however, I am still interested in
knowing if there are so many fields of a record, what is
the best way (keep a big one/divided into two) and how to
join the tables together?

Thank you for your advice.

KHLan

 

[John Vinson]

should
I design one big table to have

MachineID TimeStamp Button1 Button 2 ...60

Or to split the table into 2 or 3 smaller tables, say
Button 1 to 20 at one table

Given the discussion downthread, I'd grit my teeth, hold my nose, tell
my Relational Purist Conscience to look the other way, and use a
single table. You're allowed up to 256 fields so sixty fields, per se,
isn't prohibitive; functionally you will be worse off splitting it
into two or three tables than you would splitting it into 60 records,
so it's not worth the hassle.

 

[Duane Hookom]

I would pull the records into the database temporarily into one table and
then normalize them as Doug suggested. If your situation works un-normalized
then I guess you could keep the larger number of fields in a single record
in a single table.