The Larger of the Two Table Making Evils?...

[AggieLJ]

Which would be the larger evil:

(A) Having lots of blank cells within 1 table, or

(B) having multiple small tables with no blank spaces, but having to have complex queries accompanying them to be able to combine the data to resemble the 1 table in (A)?

Thoughts?

 

[ByteMyzer]

It depends on the scale of the data for your application. If the single table has a relatively small number of records (< 10,000) this shouldn't be a major issue. If, however, the number of records is large, with most of the fields in each record being blank, then a more normalized structure is called for.

The disadvantage to a multiple table structure is that it requires more complex queries to report on the data. The advantage is that it makes more efficient use of storage space, as well as a more performance-efficient model for storing/retrieving the data, and is far more scalable than the single-table solution.

 

[gemma-the-husky]

neither is particularly ideal

it depends on what you are trying to model - loads of small tables sounds wrong (how many is loads?) - but loads of blanks ALSO sounds wrong

can you expand on your problem?

----------
the point is, that having multiple tables DOESNT make queries more complex - it makes designing queries. and the application easier, not harder
AS LONG AS the tables are designed (normalised) properly.

and the idea probably isnt to try and reconstruct the original table with lots of blank cells

 

[HiTechCoach]

Which would be the larger evil:

(A) Having lots of blank cells within 1 table, or

(B) having multiple small tables with no blank spaces, but having to have complex queries accompanying them to be able to combine the data to resemble the 1 table in (A)?

Thoughts?

I agree that you really want neither. You would want something in the middle.

I also take into consideration how the data will need to be reported. I have found that designing your table's structures to make data entry easier is generally not a good. way. I design my structures based on reporting need s and following the rules of normalization. This will hep insure hat the required reporting is possible.

 

[AggieLJ]

There will be TONS of records (counted in 100ths of pounds).

The big concern for me is wasted space. I will be working with customers and invoices, numbered in the 10s of thousands, as well as laboratory data in the 100s of thousands of records.

For instance, it is WAY easier to make one table that has spots for two phone numbers per customer, a fax number, and an email address, but what if they have 3 phone lines or 2 fax numbers? Or even yet, what if there is not any information provided at all? My system would either fail, be riddled with "holes" in the tables, (or the phone numbers, heaven forbid, would have to be stored in two places). NONE of these seem like a good option....

I agree that the correct path lies somewhere in the middle, but how is one supposed to decide when the amount of space wasted in a couple tables is worth not having to write multiple complex queries to put together the same information from a lot (20 or so) different tables?

 

[gemma-the-husky]

normally people store phone numbers in the contacts table, i think (like a mobile phone storing home, business, etc - a few blanks dont matter)

but if you need several phone numbers, then store them separately in a table linked to the contact, rather than adding multiple phone numbers to the contacts table

one difference is that the phone number is just a bit of data, and probably not a fundamental record in your system (like an invoice). you would almost certainly store something critical to the information structure in a separate table

with regard to numbers of records, then having tens of thousands of records just isnt a problem - the data structure is.

 

[DevastatioN]

I agree with Gemma that you need a separate table called "tblContactInfo" or something which has "Contact Criteria" such as Phone Number, E-mail, etc. and then "Type of Contact" which is a dropdown for "Phone", "Fax", "E-mail" etc.

 

[AggieLJ]

Ok... how about this plan:
The only consideration (that I can think of) that a set up like this WOULD NOT account for, is the fact that, if there is a company name, more than likely there will not be a contact name (FName and LName) given...

tblCustomers
CustID
FName
LName
Address
ZIP
Phone1

tblZIP
ZIP
City
State

tblEmail (a lot do not send email information)
CustID
Email

tblCompany (we have about 75% individuals with out company)
CustID
CompanyName

tblExtraPhone (most won't have this)
CustID
ExtraPhone

tblFax (most don't have this either)
CustID
Fax

 

[Banana]

Just to point out that Gemma and DevastatioN were suggesting something like one table for *all* contacts with a flag to identify contacts.

So, instead of having three tables for email, extra phone and fax, you would have one table:

tblContacts
ContactID
ContactTypeID <- relates to a lookup table
CustID
Contact

tblContactType
ContactTypeID
ContactType

Data in tblContactType:

1 Phone
2 Fax
3 E-mail

This way, you only have one table to manage contacts and queries can be written to filter on phone, emails or fax, or a combination of such.

Hope that helps.

 

[HiTechCoach]

I would recommend that you take a look at the following example to see a working database that follows the advice you have been given.

Contacts -- Names, Addresses, Phones, eMail, Websites, Notes