I think you might be offering to contribute some of your developer time to
help Dieter build the next version - I'm sure he's willing to add people to
the project with the suitable skills and time...
-----Original Message-----
From: Roland Stoker
To: sql-ledger users mailing list
Sent: 1/3/02 10:43 AM
Subject: Database architecture...
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
|
