1. I have a database with 9 tables. My main table has about 100 fields in it,
and I am looking at adding more fields to this main table, and yes they are
all related, but I'm thinking the table is getting too big. Are there
guidlines for a size of a table?
2. The database is split, can I add another table?
Albert Einstein was once criticised for the complexity of his theory
of general relativity. He replied in words to the effect "A theory
should be as simple as possible - but no simpler."
A table should have as many fields as are required, and no more.
Sometimes a 60 field table needs more fields; sometimes a 10 field
table has too many. But - in my experience anyway - 50 or 60 fields is
an ENORMOUSLY wide table. If you have 100 fields in your table, that
implies that the Entity represented by the table has 100 distinct,
non-repeating, independent attributes. There may well be entities with
that many but they're very rare.
I would VERY strongly suspect that you're "committing spreadsheet" -
if you have fields with names like Agent1, Agent2, Agent3 or January,
February, March then you're embedding one-to-many relationships within
each row of the table. If you find yourself needing to add more fields
as you get new data, then you CERTAINLY are on the wrong track.
Feel free to post the names of a dozen or so of these fields and
describe their contents if you'ld like some help normalizing - but I
really feel that you need to normalize!
To add a new table to a split application, open the backend database;
add the table; reopen the frontend; use File... Get External Data...
Link to link to it.
John W. Vinson[MVP]
