[AccessD] Contact Database Design III.

Charlotte,

  I was just surprised that a book about an RDBMS from a very well known
author would have a sample database that most would call poor.

  The bit flags really threw me.  I'd never use separate fields for
something that should be in a single state.  To easy for the flags to get
screwed up.

  Mainly I thought this would be interesting to pass along as there seems to
be quite a few different viewpoints on it.

  As for the design I'll be using; well it's myself and the client that has
to live with it<g>.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Thursday, May 31, 2007 11:08 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Contact Database Design III.

You can always justify whatever design you use, Jim.  I once had a
boiling argument with a SQL guru at a conference because he insisted
that social security numbers change so rarely they can be used as a PK.
My argument was "rarely" is not the same as "never".  His was "it's
close enough".

I always look at what I might need to do with the data.  I worked for a
direct marketing firm at one time and designed databases for them.  They
loved the speed and simplicity of flat database design they had been
using, but their clients wanted all sorts of statistical reports that
simply couldn't be extracted from the data except through laboriously
mangling the data in Excel or importing it into an Access database and
then building a normalized schema for reporting.  They finally realized
that the important thing was how quickly they could get the information
OUT of the data, not how fast they could enter records.

Disclaimer:  your mileage may vary. ;-}

Charlotte Foust

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Wednesday, May 30, 2007 2:03 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Contact Database Design III.

Hi All,
 
  Stumbled across something today that I thought I'd pass along in
regards to contacts.  I have the book "Microsoft SQL Server 2000 Bible"
by Paul Nielsen as one of my reference books sitting on the shelf.
Happened to notice today one of the sample SQL databases included had a
contacts table.
Direct quote from a description of the database:
 
"For simplicity, all contacts are merged into a single table and the
contact type is signified by flags.  A contact can be a customer,
employee, or vendor.  Customers have a lookup for customer type, which
is referenced in determining the discount."
 
  Literally he has three bit flag fields in the record to indicate if it
is a customer, employee, or vendor contact.  It also includes name
fields and a company name, so the contact can be a person, a company, or
a person at a company.
 
  Suffice to say I was quite surprised to see this.
 
  BTW, I'm still going back and forth between doing a single table vs
one for individuals and one for companies.  Even before I had seen the
above, I was leaning towards going back to a single table.  I'll let you
know what approach I finally choose when I get to it and any pitfalls
that ensue as a result.
 
Jim. 
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com