sql-ledger-users

Database architecture...

[Roland S. <sql...@st...>]

Hi,

I'm kind of new here, so it's probably not my place to do this but I would 
like to suggest a prety large change in the db-architecture...

I'll first try to tell why. I've a business that every now and then gets 
asked to build databases. On basis of experience we have found some strange 
irregularities between what people tend to build and what would be more 
convinient.

Put simple it is about this: Logicly everybody tends to build tables with 
people or companies from there functions in the database. In this one you 
have customer and vendor for example. The fact that you use two tables for 
businesses, is redudancy in it's own and it's not just a theoraticle problem 
because it actually creates (okay, they are minor, but still) problems.

Exemple: I am a business that sells goods. I have a vendor where I buy my 
goods. What happens when he wants one of my goods??? To do it correct, I 
would have to put him in the customar-table, but that would be creating 
redundancy in itself, because I would have to maintain a second adres and all.
Other exemple. Say I would like everybody (vendors and customars) to have a 
login on my website. With what query do I check the login-name?? I cannot 
create a view because then I cannot garantee that a login name appears only 
once!


Solution: The solution would be to not take people or companies as being 
there functions. I mean, a vendor is not really a vendor because he himself 
also buys goods. He is actually a "company" that has a "vendor" relationship 
with you.
To recreate that you make a th folowing tables:

-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 = primary key, and some explanation fields)
-people (probably something like employees like adres and phone,... but 
without salary)
-peoplecompany (a bit like function to tie people to companies, like contacts)
-employee (people_nr, salary,...)
( You could also start with departments, but is not necessary)

notes
Company_nr =1 would be you're company.
In the function table you could a second company_nr, but I don't think anyone 
would be interessted in relations between two other companies.
Employee could have a company_nr, but what's the point, I am only ionterested 
in my employees so I assume there all mine....
Even adresses can be separated from people and companies if you're really 
ambitius.

Query's
Queries will not change much, other then always having to define who you want.
For exemple: select * from customar; becomes select * from companies, 
function, function_define where companies.company_nr = function.company_nr 
and function.function_nr = function_define.function_nr and explanation = 
"customar";
But now you can get a listing of all companies by: select * from companies;

Program-changes...
These can be minor at first, by just changing the query's and here and there 
cvhanging the fields users fill in. This means that you will not be able to 
use the full functionality, but it will work, as long as nobody uses the 
database directly.
Based on the new database you can add features later.

User interface
When you're adding features this will inevitable lead to a different user 
interface. But, I do not think it is going to be more diffcicult, because 
HTML and maybe a little bit of javascript (rather not) you can make the use 
of functions more powerfull.
I think that longturm this will make expensions possible like CRM (Customar 
Relation Manager). If we all become really ambitius, we could start looking 
at a standerd database-model for companies, which more seperate programs 
(used in companies) could use. For exemple, most employees wouldn't be able 
to use sql-ledger (only the bookkeepers), but most could via an 
adresbookprogram look up the adresses or e-mail from the central database. 
But for that to work the adresses would have to be in one table in a way that 
all programs use.
You could look at phpgroupware (www.phpgroupware.org) for their applications 
to work with that database-layout.

All this could create a large program consisting of many modules that every 
company could use. SQL-ledger should be the centerpeace because no company 
can do without accounting.

Should be something to think about.

Roland Stoker

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!           */
--------------------------------------------------------------------

Re: Database architecture...

[Roland S. <sql...@st...>]

Hi Thomas...

This is the last answer to you over the list, for a further discussion I 
suggest we do that privatly....

I am aware of 3NF, and why you don't always want to follow those rules, I've 
made a lot exceptions on them myself because it can be usefull (mostly for 
performance as you say).
But helas, thats not my point here. I'm not talking about performance here, 
but about using the possibilities that are offered by open-source software, 
and about keeping possibilyties open for future expansions.
Most companies would be helped if a software package could be build that 
incorporates al packages they need, using only one database. Take for exemple 
an addresbook. You need the addresses to make invoices, but not everyone has 
acces (or should have) to the accounting, so you'll need a second one for the 
other employees. But if there's a second program that uses the same tables, 
they could use that.
For that idea to work, you would need a standerd database model, and 
something like addresses would have to be in one table.
There's also something like loginnames, but I'll react to that later...
For sql-ledger to really improof from what it is now (it's the best 
bookkeeping program I've seen) is to make it work together with other 
programs and to let the idea go that everything should work inside of it. 
You've now made an order system inside of sql-ledger, but I havn't seen an 
accounting doing the ordering of hardware yet or placing the orders of 
clients. Usually that's someone else, who has no access to the accounting.
Wouldn't it be great if sql-ledger and phpgroupware could use the same 
database (and i don't mean just the adresbook, but the entire package). With 
enough willpower, it could be done within a month, because it really isn't 
that much work.

But even if you don't do something like that, the continuing expanding of 
sql-ledger as it is happening now, will mean that in the future there are 
going to be more functions for people and businesses. And creating tables 
every time is going to mean that you're database gets more complex and less 
usefull for companies who want to use all the functionality because they have 
to copy persons all over the database and have diffuculties keeping address 
correct.

> 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
> (whether 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.

I agree it doesn't really happen that often, but that in intself means that i 
DOES happen...???

> > Other exemple. Say I would like everybody (vendors and customars) to have
> > a login on my website. With what query do I check the login-name?? I
> > cannot create a view because then I cannot garantee that a login name
> > appears only once!
>
> This has been discussed previously and is a valid point.  Relational
> databases 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.)

As far as I know that isn't possible. You cannot create an index on a view, 
only on individual tables, which means that a loginname could excist twice, 
once in vendor, and once in customar.
I think we agree that coding something that checks for that problem isn't the 
ideal solution.

> > Solution: The solution would be to not take people or companies as being
> > there functions. I mean, a vendor is not really a vendor because he
> > himself also buys goods. He is actually a "company" that has a "vendor"
> > relationship with you.
> > To recreate that you make a th folowing tables:
> >
> > -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 = primary key, and some explanation fields)
> > -people (probably something like employees like adres and phone,... but
> > without salary)
> > -peoplecompany (a bit like function to tie people to companies, like
> > contacts) -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)... All of this for the rare exception?   I don't see the gain.

I'm going from 5 to 6 tables, so not that much compexity extra. If you're 
counting fields it will actually become less. But when there's a new kind of 
company or person, that's when the advantages of this system come into action.

> 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.

There's something called views. If you create a view calle vendor consisting 
of all companies that sell goods to you, you wouldn't even need to change the 
query's. Personly, I don't mind the joins. I have more problems with tables 
that shouldn't be there, making it more diffucult for people trying to make 
add-ons for you're program.

> > Employee could have a company_nr, but what's the point, I am only
> > ionterested in my employees so I assume there all mine....
> > Even adresses can be separated from people and companies if you're really
> > ambitius.
>
> vendor/customer relations may have drill down tables as well...without
> 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
> 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.  

I agree that you can have a bad model which gives significant performance 
loss. That's definitly not the case here.
If you're using an index (which was the idea), the db will search 
logaritmicly. Meaning that that if the tables become twice as long (combining 
vendor's and customars) it will need twice as long.
Exemple: You have 1 million customars and 1 million vendor's in you're db.
making the query ( to search a certain vendor using indexnumber) in the db we 
use now using 20 compare statments. The new one will use 42. 42 Compare 
statements is still using no time.
The performance towards reduction of redundancy you're talking about is 
actually called caching. This is where you deliberatly put data in a table, 
to be able to find it faster later. That's not what were discussing now.

If you see this sort of thing as too
> 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.)

I'm very fond of the relational system, and for good reason. Even dough I 
don't like using 1 to 3NF

> Simpler IS better...

Yes, but what is simpler????

> > 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
> development 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.  

I'm not suggesting anything that would take a lot of time coding. In its 
sompelest form, not even the query's would need work, only the db. If it 
needed a totaly new interface, I would be in the wrong place, because this 
interface is practicly finished and really good.

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
> combo of a sound relational structure coupled with an OO design is a clear
> winner.

Finaly!!!! Of course " a sound relational structure coupled with an OO design 
is a clear winnar". That's what I'm suggesting here. You have an object 
called companies, with certain functions in an interaction with you. That's 
what this is all about. There is no object "vendor" because thats a proparty 
of an object, and not the object itself.

Roland

Re: Database architecture...

[John O'G. <ogo...@ho...>]

You are both right.

The problem is to know where to stop.

1. re excellence of SQLledger
  I agree that SQLledger is superb. It is much much simpler than
  commercial offerings that I have had to support in the past.
  In general this simplicity is not at the expense of loss of
  function.

  Having a database that you can fully understand because it has only
  a dozen tables is precious.

  Roland's point about increasing database complexity as modules and
  features are added is very apparent in commercial examples where 
  tables are numbered in the many hundreds. I ernestly hope that
  SQL-ledger can avoid falling into this trap. I think Roland's
suggestions
  might help this - but they need to be carefully thought through first.

2. re 3NF
  This is a sound principle which can easily be pushed past the point of
  absurdity.

  For example, why stop at a separate table for addresses? I could (but
won't)
  argue that we should properly normalise addresses into 4 subtables:
   locations -< streets -< towns -< countries on the grounds that this
  would eliminate enormous redundancy (especially in countries with
large
  population and many towns/cities). 

 3. re OOP
  All OOP programming has at its heart the difficulty of deciding how
  to structure your nodel of the problem space. This is almost never
  a single correct or obviuos design.

John O'Gorman

Roland Stoker wrote:
> 
> Hi Thomas...
> 
> This is the last answer to you over the list, for a further discussion I
> suggest we do that privatly....
> 
> I am aware of 3NF, and why you don't always want to follow those rules, I've
> made a lot exceptions on them myself because it can be usefull (mostly for
> performance as you say).
> But helas, thats not my point here. I'm not talking about performance here,
> but about using the possibilities that are offered by open-source software,
> and about keeping possibilyties open for future expansions.
> Most companies would be helped if a software package could be build that
> incorporates al packages they need, using only one database. Take for exemple
> an addresbook. You need the addresses to make invoices, but not everyone has
> acces (or should have) to the accounting, so you'll need a second one for the
> other employees. But if there's a second program that uses the same tables,
> they could use that.
> For that idea to work, you would need a standerd database model, and
> something like addresses would have to be in one table.
> There's also something like loginnames, but I'll react to that later...
> For sql-ledger to really improof from what it is now (it's the best
> bookkeeping program I've seen) is to make it work together with other
> programs and to let the idea go that everything should work inside of it.
> You've now made an order system inside of sql-ledger, but I havn't seen an
> accounting doing the ordering of hardware yet or placing the orders of
> clients. Usually that's someone else, who has no access to the accounting.
> Wouldn't it be great if sql-ledger and phpgroupware could use the same
> database (and i don't mean just the adresbook, but the entire package). With
> enough willpower, it could be done within a month, because it really isn't
> that much work.
> 
> But even if you don't do something like that, the continuing expanding of
> sql-ledger as it is happening now, will mean that in the future there are
> going to be more functions for people and businesses. And creating tables
> every time is going to mean that you're database gets more complex and less
> usefull for companies who want to use all the functionality because they have
> to copy persons all over the database and have diffuculties keeping address
> correct.
> 
> > 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
> > (whether 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.
> 
> I agree it doesn't really happen that often, but that in intself means that i
> DOES happen...???
> 
> > > Other exemple. Say I would like everybody (vendors and customars) to have
> > > a login on my website. With what query do I check the login-name?? I
> > > cannot create a view because then I cannot garantee that a login name
> > > appears only once!
> >
> > This has been discussed previously and is a valid point.  Relational
> > databases 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.)
> 
> As far as I know that isn't possible. You cannot create an index on a view,
> only on individual tables, which means that a loginname could excist twice,
> once in vendor, and once in customar.
> I think we agree that coding something that checks for that problem isn't the
> ideal solution.
> 
> > > Solution: The solution would be to not take people or companies as being
> > > there functions. I mean, a vendor is not really a vendor because he
> > > himself also buys goods. He is actually a "company" that has a "vendor"
> > > relationship with you.
> > > To recreate that you make a th folowing tables:
> > >
> > > -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 = primary key, and some explanation fields)
> > > -people (probably something like employees like adres and phone,... but
> > > without salary)
> > > -peoplecompany (a bit like function to tie people to companies, like
> > > contacts) -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)... All of this for the rare exception?   I don't see the gain.
> 
> I'm going from 5 to 6 tables, so not that much compexity extra. If you're
> counting fields it will actually become less. But when there's a new kind of
> company or person, that's when the advantages of this system come into action.
> 
> > 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.
> 
> There's something called views. If you create a view calle vendor consisting
> of all companies that sell goods to you, you wouldn't even need to change the
> query's. Personly, I don't mind the joins. I have more problems with tables
> that shouldn't be there, making it more diffucult for people trying to make
> add-ons for you're program.
> 
> > > Employee could have a company_nr, but what's the point, I am only
> > > ionterested in my employees so I assume there all mine....
> > > Even adresses can be separated from people and companies if you're really
> > > ambitius.
> >
> > vendor/customer relations may have drill down tables as well...without
> > 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
> > 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.
> 
> I agree that you can have a bad model which gives significant performance
> loss. That's definitly not the case here.
> If you're using an index (which was the idea), the db will search
> logaritmicly. Meaning that that if the tables become twice as long (combining
> vendor's and customars) it will need twice as long.
> Exemple: You have 1 million customars and 1 million vendor's in you're db.
> making the query ( to search a certain vendor using indexnumber) in the db we
> use now using 20 compare statments. The new one will use 42. 42 Compare
> statements is still using no time.
> The performance towards reduction of redundancy you're talking about is
> actually called caching. This is where you deliberatly put data in a table,
> to be able to find it faster later. That's not what were discussing now.
> 
> If you see this sort of thing as too
> > 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.)
> 
> I'm very fond of the relational system, and for good reason. Even dough I
> don't like using 1 to 3NF
> 
> > Simpler IS better...
> 
> Yes, but what is simpler????
> 
> > > 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
> > development 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.
> 
> I'm not suggesting anything that would take a lot of time coding. In its
> sompelest form, not even the query's would need work, only the db. If it
> needed a totaly new interface, I would be in the wrong place, because this
> interface is practicly finished and really good.
> 
> 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
> > combo of a sound relational structure coupled with an OO design is a clear
> > winner.
> 
> Finaly!!!! Of course " a sound relational structure coupled with an OO design
> is a clear winnar". That's what I'm suggesting here. You have an object
> called companies, with certain functions in an interaction with you. That's
> what this is all about. There is no object "vendor" because thats a proparty
> of an object, and not the object itself.
> 
> Roland

Re: Database architecture...

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

On Fri, 4 Jan 2002, John O'Gorman wrote:

> 2. re 3NF
>   This is a sound principle which can easily be pushed past the point of
>   absurdity.
>=20
>   For example, why stop at a separate table for addresses? I could (but
> won't)
>   argue that we should properly normalise addresses into 4 subtables:
>    locations -< streets -< towns -< countries on the grounds that this
>   would eliminate enormous redundancy (especially in countries with
> large
>   population and many towns/cities).=20

John, you've neglected post codes!  And what about "neighborhoods"?
Here in New York we have a different "neighborhood" about every 10
meters.  I can't talk to my neighbor as he resides in lowly West
Brighton whereas I live in (posh) Silver Lake.  The two should not
be grouped together just because they share a zip code.  After all
the grass is greener on the other (my) side of the street.

--------------------------------------------------------------------
              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!           */
--------------------------------------------------------------------

Re: Database architecture...

[Martin L. <m-l...@le...>]

Roland Stoker <sql...@st...> writes:

> I'm kind of new here, so it's probably not my place to do this but I would 
> like to suggest a prety large change in the db-architecture...

In general you speak about normalization of the db. *If* there are any
plans to modify the layout of the db I agree with you that there should
be spent most time to build a fairly normalized db-design. Perhaps
http://www.phpbuilder.com/columns/barry20000731.php3 is also helpful if
you're interested in db-design.

> Solution: [...] To recreate that you make a th folowing tables:
> 
> -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 = primary key, and some explanation fields)
> -people (probably something like employees like adres and phone,... but 
> without salary)
> -peoplecompany (a bit like function to tie people to companies, like contacts)
> -employee (people_nr, salary,...)

And what if an employee(=people) becomes a company (i.e. a vendor)? IMO
it should not matter wether a person is a natural person or a company -
they should be part of one single table. And this is only one example. A
properly planned db-design is the most important base.

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

Nack. The queries are not the main problem. The content of any existing
db is it, partially due its redundancy. Porting data from an existing
(redundant) db-layout to a new one can be a nightmare.

> Should be something to think about.

With a slightly different approach: Yes.

Martin

Re: Database architecture...

[<gha...@fr...>]

On 3 Jan 2002, Martin Lesser wrote:

>                                       Porting data from an existing
> (redundant) db-layout to a new one can be a nightmare.

Have you looked at DBIx::Recordset (possibly combined with
DBIx::Renderer to create tables)?  The only published
documentation I am aware of on this perl module, is to move
an existing set of tables to a new set of tables.

Gord

Re: Database architecture...

[Andrew S. <an...@ne...>]

Here we go again.

Roland Stoker wrote:
> 
> Hi,
> 
> I'm kind of new here, so it's probably not my place to do this but I would
> like to suggest a prety large change in the db-architecture...
> 
> I'll first try to tell why. I've a business that every now and then gets
> asked to build databases. On basis of experience we have found some strange
> irregularities between what people tend to build and what would be more
> convinient.
> 
> Put simple it is about this: Logicly everybody tends to build tables with
> people or companies from there functions in the database. In this one you
> have customer and vendor for example. The fact that you use two tables for
> businesses, is redudancy in it's own and it's not just a theoraticle problem
> because it actually creates (okay, they are minor, but still) problems.
> 
> Exemple: I am a business that sells goods. I have a vendor where I buy my
> goods. What happens when he wants one of my goods??? To do it correct, I
> would have to put him in the customar-table, but that would be creating
> redundancy in itself, because I would have to maintain a second adres and all.
> Other exemple. Say I would like everybody (vendors and customars) to have a
> login on my website. With what query do I check the login-name?? I cannot
> create a view because then I cannot garantee that a login name appears only
> once!
> 
> Solution: The solution would be to not take people or companies as being
> there functions. I mean, a vendor is not really a vendor because he himself
> also buys goods. He is actually a "company" that has a "vendor" relationship
> with you.
> To recreate that you make a th folowing tables:
> 
> -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 = primary key, and some explanation fields)
> -people (probably something like employees like adres and phone,... but
> without salary)
> -peoplecompany (a bit like function to tie people to companies, like contacts)
> -employee (people_nr, salary,...)
> ( You could also start with departments, but is not necessary)
> 
> notes
> Company_nr =1 would be you're company.
> In the function table you could a second company_nr, but I don't think anyone
> would be interessted in relations between two other companies.
> Employee could have a company_nr, but what's the point, I am only ionterested
> in my employees so I assume there all mine....
> Even adresses can be separated from people and companies if you're really
> ambitius.
> 
> Query's
> Queries will not change much, other then always having to define who you want.
> For exemple: select * from customar; becomes select * from companies,
> function, function_define where companies.company_nr = function.company_nr
> and function.function_nr = function_define.function_nr and explanation =
> "customar";
> But now you can get a listing of all companies by: select * from companies;
> 
> Program-changes...
> These can be minor at first, by just changing the query's and here and there
> cvhanging the fields users fill in. This means that you will not be able to
> use the full functionality, but it will work, as long as nobody uses the
> database directly.
> Based on the new database you can add features later.
> 
> User interface
> When you're adding features this will inevitable lead to a different user
> interface. But, I do not think it is going to be more diffcicult, because
> HTML and maybe a little bit of javascript (rather not) you can make the use
> of functions more powerfull.
> I think that longturm this will make expensions possible like CRM (Customar
> Relation Manager). If we all become really ambitius, we could start looking
> at a standerd database-model for companies, which more seperate programs
> (used in companies) could use. For exemple, most employees wouldn't be able
> to use sql-ledger (only the bookkeepers), but most could via an
> adresbookprogram look up the adresses or e-mail from the central database.
> But for that to work the adresses would have to be in one table in a way that
> all programs use.
> You could look at phpgroupware (www.phpgroupware.org) for their applications
> to work with that database-layout.
> 
> All this could create a large program consisting of many modules that every
> company could use. SQL-ledger should be the centerpeace because no company
> can do without accounting.

RE: Database architecture...

[Sergio M. [GTI] <se...@gt...>]

Hi Roland,

>>Exemple: I am a business that sells goods. I have a vendor where I buy my
>>goods. What happens when he wants one of my goods??? To do it correct, I
>>would have to put him in the customar-table, but that would be creating...
>>
>>Solution: The solution would be to not take people or companies as being
>>there functions. I mean, a vendor is not really a vendor because he
himself ...

Exceptions will always exist and the rule I use when creating databases is
to satisfy the majority.  I also have clients where a vendor is also a
customer, but the percentage is so small that it is not recommended to have
a large table called companies.  This will add complexity to the appliation.

Never do a major impact to database design to satisfy a minority issue!

My .02 cents!
Sergio