O Luckys έγραψε στις Apr 18, 2006 : > how about having a company table, and company_code column across all > relevant table, although you'll have to modify your application also, which > would be an additional clause in the WHERE condition e.g where company_code > = 'which company user has logged in'. > The user has to specify while logging under which company he's going to work > on. > This way would be ideal even for your Global financial consolidations if the > mgmt requires in the due course. > other option would be of two tables, Company , Organization, where you can > have company1, org1, org2 etc., this can also be applied in the same pattern > as stated above. Thanx for your thoughts, but this would require touching 173 tables + 2,594 SQL statements in a sum of 324 programs (which sum into 125,085 lines of code) > > > > On 4/18/06, Achilleus Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> wrote: > > > > > Hi, i have run into the following problem. > > Let me describe the context first. > > > > When i joined the company(ies) i work for (a group of Shipping Mgmt/ > > Owenship/Agent companies), the only thing i was told when i started > > designing the DB/Apps was just one company. > > > > So i built everything into one single DB, and i wrote the apps > > having one company in mind. > > > > Our architecture is based on jboss3/postgresql (curenctly 7.4.12). > > There is one .ear file, which authenticates users against a lotus > > notes ldap server. > > > > At the time, the corporate organisational model was a little bit wierd: > > - Many Indepentent ownership companies > > - Many Independent Mgmg companies > > (but all busines was with one company in mind). > > > > Each App user is a member of one or more ldap groups, each group > > mapping to a mgmt company. > > > > So i ended up with > > - one DB with 173 tables > > - one DB user (postgres) > > - one .EAR application with 148,827 lines of code. > > > > Now the requirements start to change. > > The boss now bought some other types of vessels too. > > So virtually there must be N separate distinct apps, where N is the number > > of Mgmt companies (roughly one for every type of vessel), where each app > > sees and manages only its data. > > > > Moreover there are some apps that should see global data for some specific > > tables. (like the crew data, people in the crew move from one type of > > vessel to the other so they are not tied to a Mgmt company). > > > > These new requirements are of legal nature, as well as of > > operational. (People managing a type of vessels dont want to mess with > > another type, > > and auditors must see each company completely separated from the rest). > > > > Doing it with extra code would be a real pain, since i would have to > > refine > > all security/authentication based on the groups (groups@mgmt_companies) > > that a person belongs to. Also this way no inherent isolation/security > > would hold. > > > > Now i am thinking of restructuring the whole architecture as: > > - Create one EAR app for every mgmt company > > - Create one DB USER for every mgmg company > > - Create one SCHEMA (same as the USER) for every mgmt company > > (mgmtcompany1,mgmtcompany2,etc...) > > - Find a way (links/xdoclet/eclipse?) to have *one* common code base for > > the N EAR apps. > > - Tweak with jboss*.xml to map java:comp/env/jdbc/<mgmt company>DB to > > java:/<mgmt company>pgsql, where <mgmt company>pgsql authenticates > > with the corresponding DB USER. > > - Classify the tables into > > - The ones that apply to ALL mgmt companies (and leave them in the > > public schema) > > - The ones that apply *only* to a mgmt company and so create one under > > each SCHEMA > > - Load the data in *each* SCHEMA, except the tables that apply to all. > > - Define a process of "mgmt company"fying the tables in each schema (e.g. > > delete from mgmtcompany1.vessels the vessels that dont belong to > > mgmtcompany1, and so forth) > > - Resolve FK constraint issues > > - The default search_path in psql (whats the the equivalent in jdbc?) is > > $user,public, so effectively *each* EAR will hit automagically the correct > > mgmtcompanyN.* tables, or the public.* tables if these tables apply to all > > mgmt companies. > > > > With this way, the hard work is DB oriented, and not APP oriented. > > However i wonder whether someone else has gone thru a similar process, > > or if someone finds some assumption conceptually flawed. > > > > Thanx for reading, and for any possible thoughts. > > > > -- > > -Achilleus > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > > -- > regards, Luckys... > -- -Achilleus
