How many columns is too many? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How many columns is too many?

Author

Topic

darenkov
Yak Posting Veteran

90 Posts

Posted - 2004-11-03 : 08:20:14

Hi guys,

I am in the process of designing a database for an online accountants directory. Basic members are permitted to list their business for free (only simple contact info), whereas paying members get to list more details, ie business description, website, business logo, services offered etc.

I am thinking that all of this information should be stored in the same table, whether they are basic of full members, and to just have null values for the fields that will not be required by basic members.

The 'business' table has about 25 columns in it, and I am anticipating that it will have about 7,000 records when full. My concern is:

a) will this design affect performance when retrieving business listings (as all the listing types are displayed together).

b) is it ok to have all of the business records in one table, with so many null fields?

c) is it ok to also have the physical and postal address details in the same table?

I was thinking about splitting it into a 'basic' members table, and a separate 'full' members table, however I thought this would go against good design theory, as the business is one entity?!

Below is a sample of my table:

TBL_BUSINESS
------------
bus_id
bus_name
bus_address
bus_city_state
bus_suburb
bus_postcode
bus_phone
bus_fax
bus_email
username
password
newsletter(bit)
membership_type
date_joined
contact_name
contact_surname
contact_salutation
contact_email
postal_address
postal_city_state
postal_suburb
contact_phone
contact_fax

Any advice or opinions would be appreciated. - sorry about the long post!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-11-03 : 08:35:48

Good table design should limit redundancies and make the most of storage space.
So I would segregate your table.
Remember it may be 7000 records to start but may grow in time.

Jim
Users <> Logic

darenkov
Yak Posting Veteran

90 Posts

Posted - 2004-11-03 : 09:07:18

I am not sure where the redundancies are? The business contact information (or physical address) is often different to the mail delivery address (PO BOX).

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-03 : 09:17:52

But an address is an address, a phone or fax number is a number. They are the same TYPES of data. Right now you have only two kinds of address, what if someone wants to have a separate shipping or billing address? Same applies to phone numbers. What if someone wants to add a mobile phone number in addition to the existing numbers?. With your current structure, you'd have to add columns to accommodate these, and almost certainly have to rewrite application code to display them.

Creating separate tables for address, phone number, and email lets you create new types of these attributes without changing the table structure. You just add rows to the same table. You waste no space if someone does NOT have a particular address or phone type.

darenkov
Yak Posting Veteran

90 Posts

Posted - 2004-11-03 : 09:26:55

Robvolk,

thanks for your clarification. It does make sense. I will add a separate table for the contact details.

I am still a little unsure whether to split the tables into basic_members and full_members? Any ideas?

cheers

darenkov
Yak Posting Veteran

90 Posts

Posted - 2004-11-03 : 09:27:24

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-03 : 09:47:09

I'd suggest making one table for all the members that contains only the required columns for both types. The extra info that applies only to the full members would be stored in a separate table.

arpp
Yak Posting Veteran

61 Posts

Posted - 2005-02-04 : 20:54:41

Extending this discussion, how is generally an address update handled.Especially when a specific address was associated to something and the user comes back and updates this address.How do I know the past address?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-02-04 : 21:43:00

a) Too hard to say
b) NULLs are bad m'kay
c) Yes and No.

Although a business is a single entity, each level of normalisation may require more than one table to be created to protect against redundancies. When all these tables are JOINed, they represent your single business entity.

DavidM

"Always pre-heat the oven"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources