Multiple Tables

[brandie]

I hope this is in the right subforum!

This is kind a "newbie" question, but I'm curious - are there any detriments to having lots of small related tables rather than one big one? I'm creating a new database that's going to have several pieces of data that may have multiple values. In the past, we just did something like:

Area1

Area2

...

Total Area = Area1 + Area2 + etc

Now that I've taken over, I've set up things like that in related tables and used portals to show the info and used a SUM function to get the totals we need over into the "proper" table.

For the new database I'm setting up, there's going to be lots of fields that will need to be in this situation. Would it be any better optimized if I just did one related table that had all of those "repeating" fields, or if I did one table for each type of repeating info.

To help further clarify, I might have one table that is for Sources with the fields SourceName, SourceDate, SourcePhone, SourceNotes.

I might have another table that has OfficeArea, YearOfficeBuilt, PlantArea, YearPlantBuilt, etc.

Would it be better to combine all these tables or have a separate one for each? I'm trying to make things as efficient in size as possible, but also make it easy to know "where to go" for information.

Sorry if this makes no sense!

[Ender]

If the things that the different records are about is very similar, I'd use the same table to house them (distinguished by a Type field). This is especially useful if the different types will need to be summarized together, within the same report.

However, in most cases (including your example), tables are about distinct things. In these cases, they should be kept as separate tables.

[mr_vodka]

Hi Brandie,

I would suggest that you find some info on Data Normalization. Breaking everything into itw own table is not always the correct answer. However, it is definately better than using repeating fields to store data.

For example, lets say you have a buyers table and a sellers table. Does it make sense to have two tables here? Most likely they will share majority of the same fields. So it is better to just have another field there such as contacttype which you can designate as a buyer or seller. There is no need for two tables.

I dont know if this can help you, but here is a link to a video that may help you with design concepts.

Data Modeling/Relational Design

** Edit: Sorry Ender. Didnt see your post.

Edited March 27, 2007 by Guest

[Ender]

** Edit: Sorry Ender. Didnt see your post.

Hey, it's always good have multiple perspectives. Anyway, we don't usually get to bump elbows (seems you're always providing the right answer).

Huh. I always saw your avatar as chef's hat. Now it all makes sense. Maybe I need to go get a drink, eh?

[mr_vodka]

Huh. I always saw your avatar as chef's hat. Now it all makes sense. Maybe I need to go get a drink, eh?

Cheers to that Mike. Thanks.

[brandie]

Thanks for the help guys. I'll give that video a whirl tonight.

As far as keeping the file size down, is there any downside to lots and lots of tables?

[Ender]

Sure, but what do you consider "lots and lots of tables"? Managing 50 tables in a single file can be a headache (especially without a jumbo display), but that shouldn't guide the relational design. You can break up a complex file like that into logical modules, either using multiple table occurrence groups or (my recommendation) multiple files. With multiple files, you need only show the TOs from another file that are needed.

The more likely maintenance consideration comes in when you look at the overall file size and the record counts in each table. Consider importing all the data from one file to another, like for a major revision of a module. If all the tables are in the same file, this involves importing each table into the updated file. But if the modules are split into different files, you need only import records in the module that was updated. Same thing when performing a recovery, having to import the recovered data into a backup.

[brandie]

Can you tell me where I can find out more about "multiple table occurrence groups"? I'm trying to avoid multiple files if possible.

[Ender]

See if this helps:

http://www.fmforums.com/forum/showtopic.php?tid/180812/