Re: Database architecture...

[Thomas G. <to...@ad...>]

On Thu, 3 Jan 2002, Roland Stoker wrote:

> Hi,
>=20
> I'm kind of new here, so it's probably not my place to do this but I woul=
d=20
> like to suggest a prety large change in the db-architecture...
=2E..
> Put simple it is about this: Logicly everybody tends to build tables with=
=20
> people or companies from there functions in the database. In this one you=
=20
> have customer and vendor for example. The fact that you use two tables fo=
r=20
> businesses, is redudancy in it's own and it's not just a theoraticle prob=
lem=20
> because it actually creates (okay, they are minor, but still) problems.

Hi Roland,

This is called 3NF (Codd's "Third Normal Form".)  As to it being redundant
and the source of 'minor problems' is really a matter of opinion.  Ensuring
relational integrity by having each entity describe only ONE subject (wheth=
er
customer or vendor or foobar) is the practical application of the theory.
The entity described by customer is the person's ROLE...this is a distinct
role, different from the role of vendor, hence, not really "redundant".
Can the same person have two roles?  Yes...does it happen often enough to
justify adding complexity to the schema?  Not terribly often.

> Other exemple. Say I would like everybody (vendors and customars) to have=
 a=20
> login on my website. With what query do I check the login-name?? I cannot=
=20
> create a view because then I cannot garantee that a login name appears on=
ly=20
> once!

This has been discussed previously and is a valid point.  Relational databa=
ses
use check constraints to prevent corruption.  See the postgres man page on
ADD CONSTRAINT or CREATE INDEX...you could also add a stanza or two to the
existing code to trap the error before the parser prints stderr to the user=
's
screen.  This is prettier to view than a failed query dumped to the screen
(and possibly more useful for the end user.)

> Solution: The solution would be to not take people or companies as being=
=20
> there functions. I mean, a vendor is not really a vendor because he himse=
lf=20
> also buys goods. He is actually a "company" that has a "vendor" relations=
hip=20
> with you.
> To recreate that you make a th folowing tables:
>=20
> -companies (with the fields like customar or vendor)
> -function (a table with company_nr and function_nr, they are primary key)
> -function_define (function_nr =3D primary key, and some explanation field=
s)
> -people (probably something like employees like adres and phone,... but=
=20
> without salary)
> -peoplecompany (a bit like function to tie people to companies, like cont=
acts)
> -employee (people_nr, salary,...)
> ( You could also start with departments, but is not necessary)

And this reduces redundancy?  I don't see it.  From this vantage point it
increases complexity without any real gain as to access the data you would
need too many joins.  Views would contain subqueries (correlated perhaps)..=
=2E
All of this for the rare exception?   I don't see the gain.

It would be easier to simply place every contact in one table and then
define their role in the context of a transaction.  But is it necessary?
Or even useful?  It makes cursor selects (without joins) difficult when
all we want back is a list of vendors...keep it simple is a very good
motto.

> Company_nr =3D1 would be you're company.
> In the function table you could a second company_nr, but I don't think an=
yone=20
> would be interessted in relations between two other companies.

Huh? =20

> Employee could have a company_nr, but what's the point, I am only iontere=
sted=20
> in my employees so I assume there all mine....
> Even adresses can be separated from people and companies if you're really=
=20
> ambitius.

vendor/customer relations may have drill down tables as well...without=20
abandoning the concept of confining each relation to the description of
a singular entity (in this case the ROLE of the person recorded in the=20
relation.)  Although here again, it is not a matter of ambition but the
usual tradeoff between performance (denormalisation of schema) versus
reduction of redundancy (in an effort to cut overhead and eliminate data
errors).  In real world situations people often denormalise because the
performance gain is worth it.  If you see this sort of thing as too=20
redundant for your taste you might consider the hierarchical model (mumps)
as an alternative.  It is very fast, less 'redundant' and has a leaner
programming language.  Of course, it has given way to the relational model
for some very good reasons (not just Oracle's marketing skills.)
Simpler IS better...

> Query's
> Queries will not change much, other then always having to define who you =
want.

[ ... snip ... ]

No offence Roland, but what you're talking about is reinventing the wheel.
In my view, the current one (being round - and sound) suffices.  Another
point is that I've seen alot of people come along and offer suggestions for
sweeping changes to a product that has already racked up hours of developme=
nt
time and has a base of satisified users.  Usually not much comes of this.
The reason?  The current product does the job...otherwise it would not be
in wide use.  Of course, should you wish to reinvent the wheel as an
academic exercise, why not?  But I see the existing schema and code as
already being rather trim and possessing an object oriented bent.  The comb=
o
of a sound relational structure coupled with an OO design is a clear winner=
=2E

> Should be something to think about.

Good luck with your coding,  I wouldn't mind debating these points
with you over a pint - as I rather like Flemish bier (esp. Duvel!)
Maybe next time I'm in the Rheinland?

Cheers,
Thomas

--------------------------------------------------------------------
              Saint Vincent Catholic Medical Centers               =20
--------------------------------------------------------------------
Thomas Good                          tomg@ { admin | q8 } .nrnet.org
Programmer/Analyst                              Phone:  718-818-5528=20
Behavioral Health Services                      Fax:    718-818-5056 =20
Residential Services                            Mobile: 917-282-7359 =20
--------------------------------------------------------------------
/*         Rekordmeister ist nur der FC Bayern M=FCnchen!           */
--------------------------------------------------------------------