VERY basic question on Primary Key/Relationship-Long post, sorry!

[Rob Werning]

This question is about as basic as it gets! I'm wondering whether I have the concept of a Primary Key correct for the particular type of databases that we are using & would appreciate someone else's insight. Here is the situation:

My company owns/manages a number of offshore vessels. Each of these vessels has onboard a separate database to store data/create reports that are specific to that vessel. One vessel will never need access to information for another vessel. When a new vessel is built, it is assigned a "Hull Number" (not necessarily a numerical value) that uniquely identifies that vessel. It is VERY rare that this number would change & if changed, it would be to a different "unique" value. I have been using this Hull Number as the Primary Key for our databases, with the databases set up in this way:

- - - - - - - -

One table: "tblApplicationInfo" - One record existing in this table with global fields (CompanyName, logo, utility fields used throughout, etc). A field "tblApplicationInfo::HullNumber" (type Text) - initially blank in the distributed database. A "tblApplicationInfo::RecordID" field (type number) is set to a value of "1".

One table: "tblVesselInfo" - Fields holding info specific to THAT vessel (VesselName, Flag, Length, Width, etc). Initially no records in this table in the distributed database. A RecordID (type number) field is set to AutoEnter a "1" on record creattion. Relationship set between "tblApplicationInfo" and "tblVesselInfo" (equiJoin) by HullNumber.

Other tables: "tbl*Others*" - A "HullNumber" field & other fields as needed, initially no records, all tables related to "tblVesselInfo" by "HullNumber". Other TO's might be created for Lookups, etc - the "HullNumber" field is Primary Key for ALL table relationships.

- - - - - - - -

When the database is installed on a new vessel, the set up process creates a new record in "tblVesselInfo" and the vessel's Hull Number is entered (by the user doing the set up) in that record's "HullNumber" field. A global variable "$$gHullNumber" is set to that Hull Number value and that Hull Number value is also copied to the "tblApplicationInfo::HullNumber" field, immediately creating a valid relationship between these 2 tables. As records are created in other tables, the "HullNumber" field in each record is set to equal the

"$$gHullNumber" variable. Each table's "RecordID" field is AutoEnter Serial to uniquely identify that record.

There will never be more than 1 record in "tblApplicationInfo". In the database aboard the vessel, there will never be more than one record in "tblVesselInfo", with the "tblVesselInfo::HullNumber" field set to the Hull Number identifying THAT vessel. As I said, a Hull Number change is something that (almost) NEVER happens, but I have a script in place to handle that if it does (basically loops through every record in every table & changes HullNumber field). I keep reading that the Primary Key should be set to an AutoEnter Serial value, but I would think that this would not apply in this case. Actually, I considered just arbitrarily assigning a "VesselID" value (independent of the Hull Number) that would be AutoEnter on creation of the record in "tblVesselInfo" & using THAT for the Primary Key instead of the Hull Number. I would think that I could use ANY value in this field, as long as it is the same in all tables.

The Hull Number assigned to a particular vessel comes from outside our company (ABS, DNV, particular classification agency). I am assuming that someone somewhere has a database for every vessel worldwide with a "unique" Hull Number for each vessel but we are not privy to that information. As far as I know, our company uses the Hull Number to identify a particular vessel (along with the vessel's name which DOES change more frequently) rather than assigning some other "unique" company ID that I need to match.

Am I correct in concept here? It should be irrelevant WHAT value I use as a Primary Key and it does NOT need to be an AutoEnter Serial. This value needs to be set in the first (and only) record created in "tblVesselInfo", copied to "tblApllicationInfo" (Set Field script step during set up) to make that relationship valid, and set in each record (AutoEnter "$$gHullNumber" on creation) of all other tables that need to be related to these tables. In this case, there will be NO "AutoEnter" created Primary Key fields - the Primary Key (Hull Number or whatever) will be entered ONCE during set up, then will be COPIED into the correct field in all records in all other tables. There may be other fields to be used as Keys, composite Keys, etc as needed, but this one is the "Primary". ALL records in ALL tables apply to THIS vessel only....

Woof - long message! Appreciate anyone else's confirmation on this.

[Søren Dyhr]

It should be irrelevant WHAT value I use as a Primary Key and it does NOT need to be an AutoEnter Serial.

Unfortunately am I going to confuse you even more, it is not the most reliable strategy - only autoenter serials are the thing to rely on. But there is a crincle to the matter:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000128

...which makes the graph a bit misleading, the chicken foot appears opposite, on the many side...

--sd

[comment]

A very long message - but the most crucial part is still missing: how (if at all) are you going to combine the data coming from individual vessels?

In all of what you did describe, there is no need whatsoever to have a unique vessel ID, since each vessel operates on its own.

[Rob Werning]

There's the kicker I do NOT need to combine data from all vessels! In this case each vessel IS operating "on it's own" and all I really need is a value to relate tables. I think that's what is throwing me....

Ideally there SHOULD be a database that would hold data from all vessels-as it stands though, there is not. Some of the vessel data is required to be kept in paper form on the vessel itself, some is going to the office in paper form/some as pdf files/some as scanned images (signatures required). On my end (the vessel), all that's required is a database to store data on our vessel... does this make sense?

[comment]

I do NOT need to combine data from all vessels!

Then why bother? If the data is not going to be combined into a central repository, there is no need to provide means for identifying the data source. Unless I am missing something, you are very much like a person who has developed an invoicing solution, and is selling it to individual companies.

That said, I would provide some insurance for the possibility that data might need to be combined in the future. Have them enter the hull number (or any other unique ID) into a global field (or into a one-record preferences table), and auto-enter it into a field in every table that might ever need to be combined. A bit wasteful, but in the absence of any planned method for aggregating the data it's the best you can do, IMHO.

[Rob Werning]

I think I see what you're getting at. With "Allow Create new records" set on the Match Contacts side of the relationship, creating a new record on the other side and entering data into the related fields on that layout creates the record on the Match Contacts side & AutoEnters a serial into the Match Contact ContactID field. Not sure how the other files's ContactID field is getting that value though!

What I'm wondering is if I even need to worry about creating a unique value for these fields. A long as I put any value in the HullNumber field in a VesselInfo table (that will never have more than one record describing one vessel) and use that same value in ALL other records in all other tables, I've got an instant valid relationship to use for placing fields from other tables on layouts based on different tables, etc. Could use Finds or other keys to isolate record sets from other tables... Hard to explain!

[Rob Werning]

Ha, I like your analogy! In the few databases I've done so far, I've been doing as you described. I just wondered if I was missing something-just doesn't seem right for some reason! I've seen other databases written by others here and they are using the Hull Number as well - all still "individual" databases residing on each vessel & keeping track of that vessel's info only....

Thanks for the help!

[comment]

Let me put it this way: if there were only one ship in the entire world, there would be no need for a hull number.

As long as the only interaction between the vessels is that they may occasionally pass in the night, they can each assume that they ARE the only ship in the world.

[Søren Dyhr]

I'm unusually hesitant in thinking further about these aspects. A development company I mingle with occasionally, have strangly enough made a standalone system in the past ... exactly for shipping. Since satellite wan'ing back then, still were teething could they easily live with runtimes.

I have otherwise rejected runtimes as finalised solution, and only pushed prototypes as runtimes, but I were to learn that this company actually made a nice earner on runtimes.

But with todays means of communication, is this statement:

Each of these vessels has onboard a separate database to store data/create reports that are specific to that vessel. One vessel will never need access to information for another vessel.

...turn a database erroneously into an equivalent of a DTP/Wordprocessing tool, where anything keying are next to obsolete. What it is here we raise the warnings about is that no identical database should in to two places or more, because it raises doubt about the integrity of the data ... which data goes to which and why!

All right flaky communication links exists and cleaver people have found a way to this:

http://www.syncdek.com/

...some very finetuned algorithms you have to use becasue establishing these in your "kitchensink" will take forever, since it have to be bidirectional.

--sd

[comment]

no identical database should in to two places or more, because it raises doubt about the integrity of the data

So should I be worried because you too might have issued InvoiceID #123? There can be no doubt about data integrity in individual databases, if you do not combine them.

Since the method of combining is presently unknown - in fact, there are no plans to ever combine the data - I'd be reluctant to introduce complexities to the local databases for this purpose.

It's a question of balance: put yourself in the shoes of the developer who, say a year from now, has to design a system for combining reports from individual vessels into a central repository: as long as each report includes a field uniquely identifying the source vessel, it won't be too difficult - and the added complexity to the local databases is minimal.

[Rob Werning]

You are right there! It is really a waste that there is no existing database to combine this data. I've made inquiries to our US office, but as far as I know, there is nothing!! We are operating on a smaller scale here (Brazil) and it is possible that I could write a database of this type for use with the vessels operating here (28 as of now). There is definitely a need!

And, since this IS a possiblity, I'll go ahead and create THIS database with a VesselID that uniquely ID's each vessel. As you both say, it would be a good idea "just in case"....

[Rob Werning]

Correct. And the "developer" who might have to do that is ME! I think I'll go ahead and plan for this from the start - just so I don't shoot myself in the foot....

I think this is what was throwing me in the first place. I know it should be done this way, but considering the CURRENT situation couldn't come up with a reason why it SHOULD - AKK

[David Jondreau]

If you're concerned that at some point in the future you will want to combine all these databases...I think this would be a case for a uID.

[Rob Werning]

You're (all) correct. I'm going to do THIS project with that in mind! Very likely that this data WILL be combined (eventually-by me or someone else). Always great to have other people to provide comments on things like this! Even though an ID is not needed in THIS case at THIS time, need to plan for the next logical step-combining data...

Thanks for the help all!!!!

Edited August 8, 2009 by Guest