Best way to split a table?

  • Thread starter Thread starter Sapper
  • Start date Start date
S

Sapper

I am trying(?) to make a database with a table that collects data for a
specific date.
This table will have 50 fields, each field will be for the one date only,
the first field [S/D Date].
I think this might be too much? for one table, but the only thing all the
data have in common is the date.
It does have 5 "sections" Dates/Times; Tasks/Costs; manpower; Old Work; New
Work.
I thought about making 5 tables with the [S/D Date] field being common to
all and making the Relatuionship via the S/D date field, would this be
better or even correct?

Any ideas on which way to go? TIA,
 
I do not fully grasp the purpose of the database or table to give you
specific advice.

You can create the releastionship on a data (if there will be multple rows
with the same S/D Date I don't know if that would be the choice)

You can create multiple primary keys for the same table. For example if
there is a client# and each client# will have only one unique S/D date then
create a primary key on both fields might be an appropriate choice.

50 fields in one table is allot but some situations might call for that.
However it is always best to analyze you data design and break your db down
into multiple related table.

Look up Normalization for more information about db design and breaking data
down into multple related tables.
 
I am trying(?) to make a database with a table that collects data for a
specific date.
This table will have 50 fields, each field will be for the one date only,
the first field [S/D Date].
I think this might be too much? for one table, but the only thing all the
data have in common is the date.
It does have 5 "sections" Dates/Times; Tasks/Costs; manpower; Old Work; New
Work.
I thought about making 5 tables with the [S/D Date] field being common to
all and making the Relatuionship via the S/D date field, would this be
better or even correct?

Any ideas on which way to go? TIA,
Click to expand...

You're limited to 255 fields and (more subtly but actually more of a
constraint sometimes) 2000 bytes *ACTUALLY OCCUPIED* in a table, so 50
numeric fields will not hit any software constraints.

That said... 50 fields is a WIDE table. Is there any logical structure
of these "sections"? Might there be repeating "tasks/costs" - i.e. do
you have fields like Task1, Task2, Task3, Cost1, Cost2, Cost3? If so,
you have a one to many relationship which should be modeled as such.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Like you say a long table. To help clarify, each table entry contains all
the data collected from a mechanical maintenance shutdown and each field is
unique, i.e. Shutdown date; Start time; Number of tasks performed; costs of
tasks; contractor manpower per area; etc. The Shutdown date is the one filed
that ties all the other data together, this generally happens once every
three weeks. So for this year there are 22 entries. A report compiles the
data into an "Effetiveness Report". So would it be better to use the 5
"sections" Dates/Times; Tasks/Costs; manpower; Old Work; New Work and have a
S/D Date field in each and use it as the index ID?
C.D.

John Vinson said:
I am trying(?) to make a database with a table that collects data for a
specific date.
This table will have 50 fields, each field will be for the one date only,
the first field [S/D Date].
I think this might be too much? for one table, but the only thing all the
data have in common is the date.
It does have 5 "sections" Dates/Times; Tasks/Costs; manpower; Old Work;
New
Work.
I thought about making 5 tables with the [S/D Date] field being common to
all and making the Relatuionship via the S/D date field, would this be
better or even correct?

Any ideas on which way to go? TIA,
Click to expand...

You're limited to 255 fields and (more subtly but actually more of a
constraint sometimes) 2000 bytes *ACTUALLY OCCUPIED* in a table, so 50
numeric fields will not hit any software constraints.

That said... 50 fields is a WIDE table. Is there any logical structure
of these "sections"? Might there be repeating "tasks/costs" - i.e. do
you have fields like Task1, Task2, Task3, Cost1, Cost2, Cost3? If so,
you have a one to many relationship which should be modeled as such.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Like you say a long table. To help clarify, each table entry contains all
the data collected from a mechanical maintenance shutdown and each field is
unique, i.e. Shutdown date; Start time; Number of tasks performed; costs of
tasks; contractor manpower per area; etc. The Shutdown date is the one filed
that ties all the other data together, this generally happens once every
three weeks. So for this year there are 22 entries. A report compiles the
data into an "Effetiveness Report". So would it be better to use the 5
"sections" Dates/Times; Tasks/Costs; manpower; Old Work; New Work and have a
S/D Date field in each and use it as the index ID?
C.D.
Click to expand...

If each field is "atomic" - contains only one chunk of information -
and depends only on the Primary Key, then maybe you just have a
legitimately wide table. I'd recommend against using a Date/Time field
as a Primary Key; it's stored internally as a double float number and
you can get wierd roundoff problems. I'd suggest using an Autonumber
PK and putting a unique Index on the ShutdownDate field, and just
using one table. One to one relationships will probably be more
problem than benefit!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thx John, I started to do just this, but then I thought it to be bit too
clunky (wide). That's why I posted my question, it's nice to have you
"Guys-in-the-Know" to make it feel that I've done something right, so to
speak.
Thx again. C.D.
 
Back
Top