One Set or Multiple Sets?

[Thomas Jones]

Can I get some tips/suggestions from those who have travelled this road before in developing applications?

In a situation where one can create a set with multiple parent, child and grandchild tables, what are the pros and cons of creating one "large" set or two or more "smaller" sets?

For example, let's say you can foresee the need to relate a customer table (parent), invoice header table (child), invoice line item table (grandchild), shipping address table (child), cash receipts table (child), and so on. Disregarding the referential integrity issue, have you had better experiences with one set (all of the above in one set) or more than one (say, 1. customers-"invoice header-"invoice line items, 2. customers-"cash receipts, 3. customers-"shipping addresses)

Aside from the number of tables open, what other issues might one want to consider?

[Keith Hubert]

RE: One Set or Multiple Sets?

Thomas,

I was told by a very wise man that the secret is lots of sets. The simple rule is a few tables for data in and a lot of tables for data out. Build the set for what you are going to use it for, by that I mean, data Entry or data Viewing/Reporting. There would be nothing gained by having a Cash_in table in the same Set as Shipping_address. Some times you will want to build a set with the child as the parent. Have a look in Alpha Sports. If you need more help please ask.

Regards

Keith Hubert
Guild Member
London.

[Jim Chapman]

RE: One Set or Multiple Sets?

Hello Thomas,

""what are the pros and cons of creating one "large" set or two or more "smaller" sets?""

Several smaller sets are much preferable to a single large one for several reasons, especially if this is a networked application. A set creates almost no overhead, as it is simply the linking definition between tables. It will create an index for the child tables based on the linking key value if there isn't already one.

So, within reason you can create sets with impunity. The only time I will use a large set is when reporting, never for data entry.

Just my thoughts,
Jim

[Jim Chapman]

RE: One Set or Multiple Sets?


""A set creates almost no overhead""

I should have been clearer, and said:

A set definition creates almost no overhead.

A set opened of course does create overhead, and this is why they should be kept as small as practical.


Jim

[CALocklin]

RE: One Set or Multiple Sets?

As Keith said, minimal tables for data input - especially if working on a network with multiple users inputting data at the same time. In fact, the general recommendation when multiple users are inputting data at the same time is to have the input occur only in forms that are based on one table.

For output purposes, I've never heard of multiple tables causing any type of problem with the database. However, extraneous 1:M child tables can cause problems with a report because they could result in additional detail records that you don't want to have printed.

I can't think of a very realistic example right now but let's assume you have customers linked 1:M with orders and 1:M with e-mails sent. If you wanted to generate a report showing details of each order, you could end up with a bunch of blank records for each e-mail that was sent because each 1:M would be a treated as a separate record but the e-mail records would not have any order info.

[Thomas Jones]

RE: One Set or Multiple Sets?

Boy, I love these quick replies. Thanks to each of you. Jim's last comment really put this matter in perspective for me.

""A set definition creates almost no overhead.

A set opened of course does create overhead, and this is why they should be kept as small as practical.""

Thanks again.