Do I have too many fileds in one table? About 121

[JB]

I've made a table that contains:
PartNo, BalanceStart, Prognosis, Arrival, Departure, BalenceEnd
Everything but PartNo is 'repeatded' 24 times.
From PresentYear January to NextYearDecemer
eg. BalanceStartPY01...BalanceStartNY12
....
BalanceEndPY01...BalanceEndNY12

I've made a code that updates for every PartNo,
Arrival, Departure and Prognosis
This is already done.

Now Im about to calculate and update every BalenceStart & BalenceEnd.
That formula is pretty simple,
BalenceStart(i)=BalenceEnd(i-1) 'Just to get a startvalue for each
period.
BalenceEnd(i)=BalenceStart(i)-Prognosis(i)-Arrival(i)+Departure(i)

The question for me is, shoud I slpit this table in to 5 tables, each
containing PartNo and 24 fields of the same type?
(PartNo, Arrival(PY01 to NY12)
I dont want to do it, because the fields sort of 'depend' on each
other.

In Excel, I would not have wasted any time by thinking of splitting,
just worked with one 'wide' table.

I've heard people talking abot that their db got many users, and then
they say it's 10 of them. I quess Ive got the same problem now;
how much is many?

In this case, is 121 fileds many? I dont think its many for a db, but
perhaps it too many to mankind to keep track of.

When developing this table, its on an Access db, when this is done,
it will be on a SQLserver.

 

[Jeff Boyce]

JB

Your design looks like a spreadsheet -- but that is a BAD thing for a
relational database. If the design works for you, perhaps you could just do
it in Excel?

If there's a compelling reason for using a relational database (i.e., Access
and/or SQL-Server), take the time to read up on the topic of normalization.

Good luck

Jeff Boyce
<Access MVP>

 

[Jeff Boyce]

Here's a link to the MS support area -- look up KB article # 100139 for an
intro to normalization.

http://support.microsoft.com

Jeff Boyce
<Access MVP>

 

[John Vinson]

I've made a table that contains:
PartNo, BalanceStart, Prognosis, Arrival, Departure, BalenceEnd
Everything but PartNo is 'repeatded' 24 times.
From PresentYear January to NextYearDecemer
eg. BalanceStartPY01...BalanceStartNY12
...
BalanceEndPY01...BalanceEndNY12

Yes. You have too many fields; no question about it. This table
violates normalization rules by having repeated fields - a major
no-no!

I've made a code that updates for every PartNo,
Arrival, Departure and Prognosis
This is already done.

Now Im about to calculate and update every BalenceStart & BalenceEnd.
That formula is pretty simple,
BalenceStart(i)=BalenceEnd(i-1) 'Just to get a startvalue for each
period.
BalenceEnd(i)=BalenceStart(i)-Prognosis(i)-Arrival(i)+Departure(i)

The question for me is, shoud I slpit this table in to 5 tables, each
containing PartNo and 24 fields of the same type?
(PartNo, Arrival(PY01 to NY12)
I dont want to do it, because the fields sort of 'depend' on each
other.

That's another violation of normal form. Fields should depend ONLY on
the Primary Key.

How about TWO tables:

Parts
PartNo Primary Key
<part specific fields>

Transactions
PartNo <link to Parts>
TransactionDate <what's now the date in your fieldname>
Prognosis
Arrival
Departure


The Balance fields should probably be calculated using a totals query
or a running-sum calculation on a Report, and not stored in your table
AT ALL - but given that I have no idea what they mean, or what they're
balancing, perhaps I'm offbase!

In Excel, I would not have wasted any time by thinking of splitting,
just worked with one 'wide' table.

If it's a spreadsheet problem, perhaps you need a spreadsheet
solution! "You can drive nails with a crescent wrench, but that
doesn't make it a hammer".

I've heard people talking abot that their db got many users, and then
they say it's 10 of them. I quess Ive got the same problem now;
how much is many?

I've used as many at 60 fields in a table. Twice, in the past twenty
years.

In this case, is 121 fileds many? I dont think its many for a db, but
perhaps it too many to mankind to keep track of.

When developing this table, its on an Access db, when this is done,
it will be on a SQLserver.

Then I'd REALLY suggest using a properly normalized table design.

 

[Peter Arnow]

You are really making it hard on yourself with all those identical fields. I would suggest: a PartNo table listing just parts, a Balance table with Start field and End field and a foriegn key from your Parts table, a Date table with Arrival and Departure fields and a foriegn key from your Parts table, a Prognosis table with a foriegn key from your Parts table, and a junction table similar to an Invoices or Billing type of table into which you would bring all the foriegn keys of the previously suggested tables for each action taken with any particular part.