List:General Discussion« Previous MessageNext Message »
From:Richard Reina Date:May 13 2001 4:36am
Subject:Re: Hom many columns is too many?
View as plain text  
I appreciate the replies.  I have decided to keep it all in one table
for the following reason.  First, on the one hand five attributes that
describe a customers education could arguably be a separate table, since
they describe the education, however, they still describe the customer
-- Harvard educated Bob, and Yale grad Tim.  The key here in my opinion
is that you can't separate the this attribute (education) from the
customer.  Second, the nature of the data isn't so critical to need such
precision.  As you mentioned a person could buy another home and have a
second phone number. However, for our purposes he really only has one
home phone number, that is one place that he spends the majority of his
time living.  Being able to contact the customer at his second home is
not crucial -- there's also a field fro cell phones which are becoming
almost more ubiquitous than home phones.  Third, the table will only end
up having 70 fields.  Fourth, if we did strictly apply normalization we
end up with about 5 tables for each customer that would be based on
somewhat amorphous categories like lifestyle and family.  For example
does marital status belong in lifestyle or family? -- and who really
cares they all describe the customer, and all that is important is how
everything relates back to the customer.

Robert Henkel wrote:
> 
> Well with tables not normalized you will more then likely at some point in
> get redundant data at some point.  For instance I enter Joe smith with phone
> of 123-123-1222 then Joe Smith buys a new home keeps the other home and now
> has two active phone number with on table you would have to reenter
> information already in the database on Joe Smith unless you keep adding more
> columns to accommodate each new phone number(this would be very poor
> database design).  Its basic normalizing of tables that will keep this from
> happening.  Its is very dependent on the nature of the data on whether you
> need to break that table up or not.  I would find it very hard to believe
> that all the information needed for customer/contact data
> from there name all the way to where they went to high school could be
> stored in one table logically or more importantly efficiently.
> 
> -----Original Message-----
> From: ryc [mailto:iii@stripped]
> Sent: Friday, May 11, 2001 11:58 AM
> To: 'Richard Reina'; mysql@stripped
> Subject: Re: Hom many columns is too many?
> 
> I dont think the problem was redudant data. He is talking about 75 distinct
> fields that no other table contains and ALL data is related to the contact.
> 
> It makes sence that a table with more columns would have a higher overhead
> when parsing the queries, but but other than that I dont _think_ there
> should be a major problem having a table with that many columns. However 75
> does seem like it is a very obscene amount for one table, so you may want to
> consider creating one table that stores the contacts primary information
> (name, address, phone #, business name, ect... ) that is imparative to
> working with the contact, and then have another table that contains more
> detailed background information. This way if you need the information from
> the second table you can left join it, and if you dont need it you dont have
> to. Or another scheme is only insert detailed data for the contacts you have
> that information on, and leave the rest blank (in this case you wont be able
> to use left join because if the column you join on is missing from the
> details table the query will return empty, even though the contact is in the
> primary table). Either way there are trade offs, decide what is best based
> on how much detail information you have from the contacts, and how you want
> to lay out your code.
> 
> Hope that helps,
> ryan
> 
> > With relational database design you can more then likely break that 75
> > column table down into many different tables.  Look up information on
> NORMAL
> > FORM  this will help you design an effective database.  Usually from what
> I
> > know and have been taught 3rd normal form is as low a level as you want to
> > go.  But this also depends on things such as how large your db will be.
> > Sometimes redundant data is not always a bad think.  In that case a lower
> > normal form would be fine too.  Its an art not a science:)
> >
> > RH
> >
> > -----Original Message-----
> > From: Richard Reina [mailto:Richard@stripped]
> > Sent: Friday, May 11, 2001 1:26 PM
> > To: mysql@stripped
> > Subject: Hom many columns is too many?
> >
> >
> > I am designing an in house database app. for sales/contact management.
> > We've identified out about 75 things to know about a customer/contact
> > from there name all the way to where they went to highschool.  Should
> > all these attributes be in one table since they all describe the contact
> > or should they be broken up into seperate tables like education,
> > professional backround,  business backtound special interests?
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> >    http://www.mysql.com/manual.php   (the manual)
> >    http://lists.mysql.com/           (the list archive)
> >
> > To request this thread, e-mail <mysql-thread73534@stripped>
> > To unsubscribe, e-mail
> > <mysql-unsubscribe-RobertH=marvin.com@stripped>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> >    http://www.mysql.com/manual.php   (the manual)
> >    http://lists.mysql.com/           (the list archive)
> >
> > To request this thread, e-mail <mysql-thread73536@stripped>
> > To unsubscribe, e-mail <mysql-unsubscribe-iii=binary.net@stripped>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> 
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
> 
> To request this thread, e-mail <mysql-thread73546@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-RobertH=marvin.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Thread
Hom many columns is too many?Richard Reina11 May
  • Re: Hom many columns is too many?Eric Fitzgerald11 May
    • Re: Hom many columns is too many?Gary Bickford15 May
      • Re: Hom many columns is too many?Jeremy Zawodny15 May
RE: Hom many columns is too many?Robert Henkel11 May
  • Re: Hom many columns is too many?ryc11 May
RE: Hom many columns is too many?Robert Henkel11 May
  • Re: Hom many columns is too many?ryc12 May
  • Re: Hom many columns is too many?Richard Reina13 May
RE: Hom many columns is too many?Robert Henkel14 May