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.