From: Martijn Tonies
Date: November 14 2008 3:45pm
Subject: Re: normalised designs: customer database
List-Archive: http://lists.mysql.com/mysql/215213
Message-Id: <021501c94670$14a30800$9902a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit


> > This is where it gets nasty. A customer may be a human being or a
> > company. I see different approaches here:
> > 1) keep customer tables separate, based on which type of customer it is
> > 2) create the customer table with a column specifying if we're dealing
> > with a human being or a company
> > 3) create the customer table with a FK for people and a FK for
> > companies, and decide on the customer type in the application based on
> > the presence of that key
> >
> You're making it more complicated than it needs to be.
>
> A customer may be either a person or a company.
>
> Your customers table may contain columns that are the union of what is
> required for a person and what is requried for a company, plus of course
an
> enumerated value that indicates which the customer is and indirectly which
> columns are populated for a given row.
>
> Problem solved.  Over time, several square millimeters on a disk wasted.

Back to "database design" class for you ;-)

Store in a table what you need to store, storing "which columns are
populated
for a given row." is complete rubbish.

By retrieving data you should -know- what data it is, not having to retrieve
a value that indicates what the data actually means.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Sybase
SQL Anywhere, Oracle & MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com