Making new primary key for database - need help

[kagoodwin13]

My database desperately needs a new customer primary key.



The problems I am having right now is that there are multiple departments at my company using the same data, but in different ways. Sales and finance has one version of the customer primary key, support has another version of the primary key, and IT has the master version of the primary key. The bad thing is, due to predecessors before me, none of these primary keys match, nor meet all applicable business requirements company wide.

There are two primary variables for the primary key in our master (Oracle) database - schema text, and organization ID. Schema text is usually a 3-7 digit text abbreviation of the customer name (ABCDE, GABOYZ, CBA), and organization ID is a 3-4 digit number (999, 1000, 1430).

The complexity comes in where there are multiple billing organizations per Oracle schema. For instance, within schema ABCDE, we may treat the entire schema as one billable organization, so no problems there. However, within schema GABOYZ, we may bill organizations 1003, 1786, and 1992.

For IT and support, all they really care about is schema, so all they track is GABOYZ. For sales and finance, we need both GABOYZ and 1003 to ensure that we are addressing the correct organization.

We've tried using Account Name, but that is even more unreliable. "Customer A, Inc." becomes "Customer A, Inc" or "Customer A" or simply "CA." We also have several customers with very similar names, such as "Transition Programs, Inc." and "Transitional Programs, Inc." Very easy to confuse, especially as we scale up.

I could use a concatenated schema+organization ID as in (GABOYZ1003), however 1003 may have to change to 1010 at a future date if there is a system update/upgrade, so I'm not sure if that will be robust enough.

My suggestion is to make an auto-number field and link that to schema and org, with schema and org being custom fields that can be changed later without breaking the relationships. As in, 42 links to schema GABOYZ and organization ID 1003. If organization ID changes to 1010 later, all it would take is one update to one field.

Any other thoughts?

[June7]

I vote for autonumber as PK. The customer identifier(s) should not be the PK for linking related records.

Business procedures must be in place to assure the correct customer record selected. So that data for "Transition Programs, Inc." not associated with "Transitional Programs, Inc." and for determining if a customer already in database. So verify address, phone, email, SSN, EIN, etc. - maybe customer knows the CustomerID previously used.

[orange]

Tell us more about your business and your organization. If you have multiple departments and each is using Oracle as their DBMS you will have some experienced DBAs. I am surprised that there is no corporate data management function. I 'd like to hear more about how system planning, system development is being done.

[kagoodwin13]

Tell us more about your business and your organization. If you have multiple departments and each is using Oracle as their DBMS you will have some experienced DBAs. I am surprised that there is no corporate data management function. I 'd like to hear more about how system planning, system development is being done.

I think I am the corporate data management function A lot of my job is combining the different data sources together for reporting purposes.

Oracle only houses our primary product. We are a cloud database services company, and the backend of the web interface is stored in Oracle.

The other departments use different cloud services for their functions. Sales and finance uses Salesforce and Intacct, customer support uses Soffront and JIRA (running in Oracle), HR uses PayCor, project management uses Excel, etc. We're a pretty small company with limited resources, so rather than use development resources to build customized business solutions inside of Oracle, we all scattered into our own systems. While we're all able to work in our own systems well, we run into a lot of problems when trying to connect information together. That means a lot of problems with reporting and customer service.

My preference would be that we at least combine some of these systems together. Support and project management could tie in with Salesforce, which can also be connected with JIRA for support. I think the "final solution" would be to develop a customized ERP-type system out of Oracle, but that would likely cost a lot more money for no additional value.