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