"Best Practices" question

[reynolditpi]

In one table I currently have 5 "groups" of data that share common fields but also have 20+ unique fields in each group. This has made the table what i would consider large (150+ fields). I may eventually add more groups and therefore more fields

I should also note that although these "groups" of data share common fields, one group of data is unrelated to the other. Also, the current table is the parent in a set. Therefore, spliting the data into seperate tables would mean creating more sets.

Is it better to keep them in one large table that has all of the fields or create seperate tables?

I hope this question is not too confusing.
Tony

[aschone]

Re: "Best Practices" question

In this case I would base my decision off of a couple of factors.

1) Data manipulation, How easy is it for you to manipulate the data when it is all in one big table versus several small tables?

2) Data retrieval as tables grow in size the amount of time it takes to return records will increase. Usually you find that point after it becomes a problem. Will splitting tables case the number of records in the tables to be smaller than if all the records where in the same table?

3) Data reporting, I will design my tables based on how I want reports to function with the data. I like to design my reports so that as little data manipulation as possible takes place prior to report preview. The data manipulation can be effected by the table design.

4) Form design, since forms must be based on a table, will having all detail in one record structure cause problems when viewing the data on the form?

In table design mode, I attempt to predict what I want to do with the data in th end. Then I design my tables to allow me to reach the end goal in the easiest manner possible. If I struggle with finding what I consider to be an efficient design, then my first goal is to simply collect the data. Once its collected then I worry about manipulating it. Usage of temporary tables can be a benefit during the data manipulating stage. You can also use a temp table for report generation if your main data structure doesn't lend its self well to the report generation.

Forgot to add that there are theories on database design. The theories are referred to as normalization of a database. There are, I think, 4 steps to normalizing a database. If you ask my database design teacher from college, Im sure he can tell you how lousy I did on that section of the class. Personally I usually just watch out for fields where several records contain the exact same data over and over. If I see that happening, then I start experimenting with how to move that chunk of data from the main record and into a child record.

[reynolditpi]

Re: "Best Practices" question

That helps. Thank you very much.
Tony

[DaveM]

Re: "Best Practices" question

I currently have 5 "groups" of data that share common fields but also have 20+ unique fields in each group.

is it possible to use the same fields instead of the unique 20+ fields? Possibly 1 key field to designate the group? Less fields is a good thing.

If you are adding tables for these 5 groups and expecting more, the file size could grw way too big. Also, it is not good practice to add tables for each group, year, etc. You usually use some sort of definitive key so you can use that 1 table to hold that data.

Been normalizing data for years. Not the best at it for sure and there are better ones here.

Dave