PDA

View Full Version : Single vs Multiple Tables


ABC123

michaelwpayton
12-13-2007, 01:03 PM
I'm interested in hearing thoughts/preferences/considerations, etc., based on your "Alpha experiences," relating to the age-old question of if one should have multiple tables, one for each "type" of record... or "multiple-record types," in a single table... assuming the records to be managed are similar in nature and there are a number of similar processes to be supported regardless of "record type."

E.g.

Single Table Design

Table - People
Fields - RecID, RecType (Prospect, Customer, Vendor), Org, Name, Addr....

Multiple Table Design

Table - Prospect
Fields - RecID, Org, Name, Addr....

Table - Customer
Fields - RecID, Org, Name, Addr....

Table - Vendor
Fields - RecID, Org, Name, Addr....

Developer efficiency, vs application performance, vs maintainability, vs expandability, vs flexibility, vs....

Thanks in advance,

Peter.Greulich
12-13-2007, 02:43 PM
Mike,

In the example you cite, I would use one table. In my own case, my company had a two table 1:M set:

Clients==Contacts

"Clients", although mostly clients (i.e. client companies), also contained vendors, utility companies (who we performed research at regularly), and other consultants that we dealt with. These are identified in the "type" field identifying their respective categories. Contacts were the individual people that worked for a "client" company. Some clients might have up to a 100 contacts or so. Originally we had separate tables for each "type", but it soon became evident what a maintenance nightmare it was, and all the forms, reports, etc. were being duplicated, although never quite 100% consistent. Consolidating all these guys normalized the process and interface. There may be other circumstances where the "duplication" method may be preferable. But seemingly, more & more, I find myself consolidating tables and differentiating record types.

My 2-cents.

michaelwpayton
12-13-2007, 03:30 PM
Mike,

In the example you cite, I would use one table. In my own case, my company had a two table 1:M set:

Clients==Contacts

"Clients", although mostly clients (i.e. client companies), also contained vendors, utility companies (who we performed research at regularly), and other consultants that we dealt with. These are identified in the "type" field identifying their respective categories. Contacts were the individual people that worked for a "client" company. Some clients might have up to a 100 contacts or so. Originally we had separate tables for each "type", but it soon became evident what a maintenance nightmare it was, and all the forms, reports, etc. were being duplicated, although never quite 100% consistent. Consolidating all these guys normalized the process and interface. There may be other circumstances where the "duplication" method may be preferable. But seemingly, more & more, I find myself consolidating tables and differentiating record types.

My 2-cents.

Thanks, Peter. We have done it both ways also. Within my group it seems to be an age thing, lol.

Myself (48) and my partner (52) always seem to lean towards to consolidating tables and using record types. Our, "younger," developers always seem to bring us designs that are "table ridden." Our most senior developers (regardless of age), could care less and just want us to make a decision so they can "get on with it." :)

Stan Mathews
12-13-2007, 03:44 PM
One concern not always mentioned is the 2 gig record limit inherent to the dbf format. This may or may not be relevant in your efforts.

A more practical concern is file size. Most operations, the updating of indexes, querying, etc involve the creation of temporary files. The larger the table, the larger the temporary file and thus the more time consumed for these activities.

I've never spent any time gauging the trade off but files of over 200 meg with several indices can be irksome.

michaelwpayton
12-13-2007, 03:54 PM
One concern not always mentioned is the 2 gig record limit inherent to the dbf format. This may or may not be relevant in your efforts.

A more practical concern is file size. Most operations, the updating of indexes, querying, etc involve the creation of temporary files. The larger the table, the larger the temporary file and thus the more time consumed for these activities.

I've never spent any time gauging the trade off but files of over 200 meg with several indices can be irksome.

Nice size files :)

We have done some very large apps... multiple million+ record tables... but never in dbf format. This/Alpha, is our first exposure to dbfs. Thanks for the head's up.

Doug Page
12-13-2007, 08:56 PM
Within my group it seems to be an age thing

Considering your background (and I would guess your partners is similar), stuffing everything in one file "USED" to be the norm. Hardware was much slower for opening and closing all those files. And who every wanted to keep track of all the tables since relational DB's were not as sophisticated as now. Heck, I remember one system that used the record type field as you mentioned above and the records had variable lengths.

It really is determined by the project and how much flexibility you want to give yourself for the future. I have found that an address list, as you proposed above, can be different for different groups. So you add a field for the vendors and of course it is created for every prospect and customer as well. For security reasons, you could give the sales staff permissions to look at the customers and prospects but can logically stop them from having any access to the vendor file.

Frankly, I have used both methods - some of which have never needed to change and others that had to be pulled apart later. So if you really don't think that there will be much change in the structure at a later date, I still find having it all in one easier to deal with. But that is usually when the client says, "Oh, by the way, can we ..."

NoeticCC
12-14-2007, 04:06 AM
So if you really don't think that there will be much change in the structure at a later date, I still find having it all in one easier to deal with. But that is usually when the client says, "Oh, by the way, can we ..."

LOL thanks for making my day, I get that all the bloody time with the company I work for!!! :eek:

michaelwpayton
12-14-2007, 05:19 AM
Frankly, I have used both methods - some of which have never needed to change and others that had to be pulled apart later. So if you really don't think that there will be much change in the structure at a later date, I still find having it all in one easier to deal with. But that is usually when the client says, "Oh, by the way, can we ..."

yep, we've all been there...

DaveM
12-15-2007, 04:23 PM
another posibility is to make several tables as a one to one link and avoid the file size limitation up front. That 2 gig can come up fast when you have many records and it is not just the dbf. The indexes grow even faster sometimes.

Dave