| Subject | Re: [firebird-support] Re: Primary Keys - [was] Database File Size |
|---|---|
| Author | David Johnson |
| Post date | 2004-05-25T03:34:09Z |
I am sorry if this got posted twice - I have not seen it after two hours so I reworked and reposted.
SiteID TableID presumes that a single entity has control over the site ID. These systems are disconnected because they belong to different companies, many of whom are competitors with each other. Site ID is, itself, highly variable because a "site" is a temporary location that varies from job to jab, and even within a job. Furthermore, a contractor may have several jobs at the same site. A "site", other than head office, lasts from 1 week to 2 years. If every customer enters his own "site" in freehand, then I need to add cross reference tables to allow each company's site description to be cross referenced to every other company's. Different companies do have overlapping job descriptors, and those descriptors would have to be reconciled when data from multiple contractors was merged.
Furthermore, once the job hits the field, the field system must be able to reliably function for most of the job without direct communication back to the home office. It must be able to merge selected data reliably with data from other contractors that are using the same framework on an intermittent basis in an environment where you may or may not have a cell phone connection, and probably don't have anything else.
The use of a GUID primary key creates the basis for a robust mechanism for this demanding environment, and at the same time eliminates much of the work involved in merging dis-similar and disconnected databases. There is a small performance penalty and a large index size, but not unreasonably so for the work involved.
When an entity is created, it has a unique key that is independent of any one system. The "bid", and later the "job" contain the location information. That information may be replicated on other jobs, but since the jobs are guaranteed unique in spite of those identical data elements, that is fully permissible. Any system referencing that GUID, or any GUID attached to that job, anywhere in the world, is automatically referencing the right job.
Since the GUID is the primary key for all entities in the system, there is no ambiguity and no duplication. A purchase order, invoice, or line item issued against the job by any company in the chain is fully auditable as it passes from the original purchaser through various contractors to the final owner, with no operation more complex than an SQL insert. Since the only update permitted to records under normal circumstances is to invalidate them (no deletes, no content changes) the issues of ambiguity that many distributed systems contend with mostly disappear, and the audit trail is guaranteed to be absolutely clean no matter what happens.
The potential user base for this product is every engineering firm and construction contractor in North America, from a "mom-and-pop" operation operating out of a spare bedroom to a world enterprise operation like Royal Shell. And yes, when Royal Shell has a large job they do require all of the information from the mom-and-pop subcontractors for their audit, so such a merge operation is not only reasonable but very likely.
[Non-text portions of this message have been removed]
> Why not to build PKs like (Site_ID, Table_ID) in this case? ThisLow end scenario - An engineering firm puts a project up for bid to 5 general contractors. Each general contractor gets at 3 to 5 bids from anaverage of 6 subcontractors. Some of the subs will bid to several of the generals. Because there are conflict of interest and non-disclosure rules, every computer system is independent and agnostic of every other computer system in the scenario. Each database is dissimilar because each business is different. Each of the companies involved will have an unknown number (1 or more) physical sites, some of which will be permanent, and some of which will last only for the duration of the job. It is desirable to have controlled sharing of certain data, but not full data sharing because of trade secrets and confidentiality laws.
>not only provides uniqueness but advantages in further grouping and
>identifiying "owner" of the record too.
SiteID TableID presumes that a single entity has control over the site ID. These systems are disconnected because they belong to different companies, many of whom are competitors with each other. Site ID is, itself, highly variable because a "site" is a temporary location that varies from job to jab, and even within a job. Furthermore, a contractor may have several jobs at the same site. A "site", other than head office, lasts from 1 week to 2 years. If every customer enters his own "site" in freehand, then I need to add cross reference tables to allow each company's site description to be cross referenced to every other company's. Different companies do have overlapping job descriptors, and those descriptors would have to be reconciled when data from multiple contractors was merged.
Furthermore, once the job hits the field, the field system must be able to reliably function for most of the job without direct communication back to the home office. It must be able to merge selected data reliably with data from other contractors that are using the same framework on an intermittent basis in an environment where you may or may not have a cell phone connection, and probably don't have anything else.
The use of a GUID primary key creates the basis for a robust mechanism for this demanding environment, and at the same time eliminates much of the work involved in merging dis-similar and disconnected databases. There is a small performance penalty and a large index size, but not unreasonably so for the work involved.
When an entity is created, it has a unique key that is independent of any one system. The "bid", and later the "job" contain the location information. That information may be replicated on other jobs, but since the jobs are guaranteed unique in spite of those identical data elements, that is fully permissible. Any system referencing that GUID, or any GUID attached to that job, anywhere in the world, is automatically referencing the right job.
Since the GUID is the primary key for all entities in the system, there is no ambiguity and no duplication. A purchase order, invoice, or line item issued against the job by any company in the chain is fully auditable as it passes from the original purchaser through various contractors to the final owner, with no operation more complex than an SQL insert. Since the only update permitted to records under normal circumstances is to invalidate them (no deletes, no content changes) the issues of ambiguity that many distributed systems contend with mostly disappear, and the audit trail is guaranteed to be absolutely clean no matter what happens.
The potential user base for this product is every engineering firm and construction contractor in North America, from a "mom-and-pop" operation operating out of a spare bedroom to a world enterprise operation like Royal Shell. And yes, when Royal Shell has a large job they do require all of the information from the mom-and-pop subcontractors for their audit, so such a merge operation is not only reasonable but very likely.
[Non-text portions of this message have been removed]