We developed ERP package for one pharmaceutical company for its one manufacturing location.
We use oracle9i as database, as we create module wise schema in it. Now our customer wants same package for its manufacturing location, which is in same campus of previous location.
Some of the business processes are different at there. Now following are the questions regarding this.
1. Should we create new database for this?
2. Should we create new schema?
3. Should we create new tables in the previously created schema?
4. Should we use same table for this?
2 - 3. New schema? Would your new tables, procedures, ... use anything (data, procedures, functions, ...) that exists in existing schema? Do you plan to bind new tables with old ones using foreign keys? If so, you could create new objects in old schema.
4. If business requirements are such that new ones differ from old ones in a matter of loaction identifiers, you could use existing tables. If they are much different from old tables, I'd
rather create new tables.
I'm with gbrabham on this one - all the options (new Db/new tables/new schema etc) are possible;
To help decide, you should find out what the company are happy with - do they want to protect the integrity of the 2 sets of data, do they want to keep at least one of the sites going in the event of a problem? If so, then go for 2 Db's. Can they cope with losing a Db for a few hours, and are not fussed about the 2 sets of data being on the same Db? If so, go for 2 schemas. Is the data structure identical for both packages? If so, why not share the existing tables.
The ultimate choice depends on the business needs - I suggest you do a Cost/Benefits Analysis i.e. draw up a list of the options available, & for each option list the advantages/disadvantages; then let the company decide. (I can guarantee you that if anything goes wrong the company will be looking to blame someone, so don't give them the chance to blame you! If it goes pear shaped then at least you can say it wasn't because of a bad decision on your part.)
90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.