Database Planning (1 Viewer)

[wallis1905]

Autonum PK problem?

Ok I'm changing the primary key for a start

Quote
Now base your instrumentation tables on that starting point. You might have one table for each TYPE of instrument. This relationship would then be SPARSE in the sense that over the sum of all instrument sub-tables, you would fully reference every instrument at your complex, but no instrument would appear in two sub-tables at once.

Hi again. Can you tell me if you would use autonumber to form the relationship in each case? I have tried to do this and it works good bar one problem. The link seems to be good and the secondary table gets populated as intended. However, because the autonum value for a record in the main table will be different than that of its counterpart in the secondary table. This means that when I try to view a record through a query(ie some fields from the main table and all fields from the secondary table) I see nothing. The join properties filter out all the records. Obviously if the ID numbers matched it would be ok. Am I being silly?

 

[wallis1905]

I was silly

Yes I was being silly, you just make the related field in the secondary table a number, and not an autonumber I think?

 

[boblarson]

You are correct, the secondary table stores the key from the primary as a LONG INTEGER.

 

[The_Doc_Man]

This is a step in the right direction. Let me address your question about dependency in the sense of KEY dependency.

Let's say, just for a silly minute, that one of your machines is a mechanical stress tester. It's unit of measure is (weight-units) perhaps with a (length-unit) or perhaps not - depends on crushing, torsional, shear, or other types of stress. But let's say it is Tons (USA measure). So you have a set of three fields somewhere (I vote for the main table) that say (0, 500, Tons)//(min, max, units) for this instrument.

In the DATABASE sense, they depend on the primary key of the table in which they appear because (1) they are proper attributes of the instrument (2) a different row with different PK would probably show different units (3) you are not likely to have these same fields on something that isn't an instrument.

The question of dependence often turns into one of RELEVANCE. If the field is relevant to the thing being described in the table; and is not itself the PK or a member of the PK; and isn't excluded from that table by normalization; then it properly depends on the PK.

Another test is to assure that the tail isn't wagging the dog. With your original compounded PK, the fields essentially described themselves in a backhanded sort of way. The instrument you defined wasn't defined by a row in a table. It was defined by a location / descriptive string. OK, for a primitive database, this can work. For a while. But eventually you realize that you have just patched a blowout on your tire by tacking on the extra number to handle what would otherwise be duplicates.

Now, let's see what this change does for you. First, your 6-element key became a single, monolithic key used always and only for linking. Next, the 6 elements have the freedom to be independent fields again. But even better, you just lost your uniqueness discriminator - because the PK IS unique. You don't need the discriminator field any more.

Your key just became capable of using a simple, single-field index. Which means very easy relationships. (Single-field relationships are ALWAYS easy from the Access mechanical viewpoint.)

This isn't to say that the table structure got simpler. (You're still working on that one...) But now you know where things go. And now you know how to find everything. (Relationship through your NEW instrument tag number, which can be the autonumber PK.)

Here, a question often comes up.... WAIT, WASN'T AUTONUMBER SUPPOSED TO HAVE NO MEANING? Well,... caught me on that one. But which is the chicken and which is the egg? If the meaning accrues AFTER you generate the record (Autonumber) instead of BEFORE (your original 6-part meaningful key), it is not technically incorrect. You are free to assign meaning to an autonumber after the fact as long as you don't visualize the number doing something that autonumbers don't do. (Be contiguously assigned, e.g.)

Just remember that if you had a linearly assigned number based on the strict chronological order in which the instrument arrived on site, but due to lifetime or accident issues you retired some instruments from the middle of the pack, you would not have contiguous numbering in that case, either. So don't demand that attribute from the autonumber.

 

[wallis1905]

Progress?

Yes, Thats good Doc man,
What you say about relavence, makes me think that at least my "main table" is not in too bad shape. I believe all the fields obey the rules you outlined.

The automumber PK I am now using will have no meaning outside the database. In my mind it simply replaces Tagnum as a means of linking tables. Tagnum still remains in the tabe and holds all the previous meaning it had. And because the new PK value will not change, then it means I don't have to worry about cascading updates and RI. It was actually RI that made me realise that I could not use tagnum as the PK. Each time you enforce RI you create an index in the parent table. I quickly ate up the 32 available. Using autonum this is not an issue.
And about the tagnum, I will still keep the descriminator field. This field is not purely to make the database operable. In practice, on site all instruments need a unique tag anyway or the control sytem will go belly up.

So what do you reckon about having a table for each instrument type like you said before? If this is ok, then I might not be too bad. I have already normalized certain groups of information. For examle the secondary table Flowmeters, now has a lookup table for the fields MeterType, and MeterOptions.
Features of current design
- One main table
- A secondary table for each instrument type, linked via Autonum.
- Generally two lookup( or normalization?) tables per secondary table. So I will have two lookup fields in each table. Each lookup table contains data that is solely dependant on the associated lookup field and not the PK.

So does this mean that if a field in any secondary table does not occur in the main table, and I can enforce a means to stop any overlap between secondary tables (Unless the overlapping tables, only overlap the Autonum and have completly different fields). Then this is taking shape?

 

[The_Doc_Man]

(Unless the overlapping tables, only overlap the Autonum and have completly different fields).

This doesn't happen. In the overlapping tables, the key is FOREIGN and therefore depends on something outside the table. (The entry in the main table, e.g.) There WILL be no overlap unless two of the child tables happen to reference the same physical object. Which, if your database is properly normalized and your tables are tightly specified, should not happen any more.

So what do you reckon about having a table for each instrument type like you said before? If this is ok, then I might not be too bad.

As a stop-gap measure during metamorphosis, this might be acceptable. Your next step is to minimize the different types of tables, each of which holds supplemental information unique to an instrument type. So, maybe your next step is to decide whether an IR spectrometer and a visible-light spectrometer fall in the same table with just differences in units and the presence or absence of paper graphic capability. (IR does, visible light usually does not.) That sort of thing.

Your end goal HAS to be to minimize the number of child tables and migrate data to the main table where appropriate, or move it OUT of the main table where appropriate. Not that you shouldn't have what you need. But you need to be ruthless about where it goes.

Let's take a possible issue: Suppose you have a maintenance table. It should link to the main table via the device's PK (to show what was serviced) but have date, maintainer, time of service action, and (maybe) a memo field to report what was done. Alternative to memo fields: A child table linked to the maintenance action's PK and with a list of steps performed so you could list as many or as few steps as needed and be able to break them out as line-items in an after-action report.) You might want this table to have a separate autonumber PK since otherwise you run into the same issue of synthesizing keys rather than just using them as FKs. This should be a one-size-fits-all table if possible. At no point should you allow data to creep in that is machine-type specific except as words in the memo field.

Another thing you need to assure is that the UNION of all of your device-class tables completely overlaps the main table. That is, you have NO entry in the main table that doesn't have an entry in ONE of the device-class tables. (Also, if properly normalized, no more than one such entry.)

As you explore your business model, you will find things that you need to track and will be able to identify them as entities of a certain class. For instance, suppose that you have a list of possible consumables involved in a service call. You would have an inventory of consumables and would be able to assign consumption records linked to the inventory table AND to the main device table (or better yet, the above-mentioned maintenance action table) so that you would have many-to-one mapping of consumables for a given action.

See where this could go?

 

[wallis1905]

Thanks Again Doc Man,

Quote:
(Unless the overlapping tables, only overlap the Autonum and have completly different fields).

This doesn't happen. In the overlapping tables, the key is FOREIGN and therefore depends on something outside the table. (The entry in the main table, e.g.) There WILL be no overlap unless two of the child tables happen to reference the same physical object. Which, if your database is properly normalized and your tables are tightly specified, should not happen any more.


Quote
There WILL be no overlap unless two of the child tables happen to reference the same physical object

Are you sure? I will have some tagnums that appear in more than one child table, but whenever this occurs the two child tables involved will have a completely different set of fields. Does this break normalization rules? There is no field repeated for a given tagnum.

And about your second point, I am currently doing just that. There are some tables that can me merged as suggested by yourself. I reckon I can loose about five or six tables. Are you suggesting a much bigger reduction?

Quote
That is, you have NO entry in the main table that doesn't have an entry in ONE of the device-class tables

This worries me. I will actually have quite a number of tagnums that will not appear in any child table. Whats the problem with that? I see these child tables as simply being an extension of the main table. If there is no data that requires an extension then is that not a bonus?

Also, can you clarify something fundamental for me. What do you think is the best way to populate the child tables? By this I mean, how can you control what ends up in a child table. At the moment it is done with a query. (I know you will say use a form, but I assure you I am stuck with queries!) The query looks at a few fields from the main table, and all fields from the child table. This query will have an appropriate filter on specnum. Now if I enter a new tagnum in the main table (with the correct specnum entered also), and then run the query I will, see the tagnum and the other fields from the main table with data in them, and all the fields from the child table blank. At this point the record has not made it's way into the child table yet. Only when you enter something into a field from the child table does the record become part of the table. Is this good practice or is there a more elegant way? The obvious problem as discussed before, is if a spec num changes(ie entered incorrectly initially) now the record will be in two child tables. Do you know any ways to prevent this from occuring?

Quote
You would have an inventory of consumables and would be able to assign consumption records linked to the inventory table AND to the main device table (or better yet, the above-mentioned maintenance action table) so that you would have many-to-one mapping of consumables for a given action.

By many to one here do you mean that many actions would use the same consumable? And what do you mean by consumption records, just say in my case a tagnum that can use a consumable, or something different?

Sorry for all the Questions. I'm slowly getting there though!

 

[The_Doc_Man]

I will actually have quite a number of tagnums that will not appear in any child table.

Hmmm... We are still talking at cross-purposes, then. I have interpreted your device-specific tables differently than this. As I see it, each of these extra device-class tables is a child of the main table that shows location data (building,floor,room), instrument type, etc. To ME, if the key in the table is a FOREIGN KEY to another table where that same key is PRIME (and an autonumber), then the table with the FK is a child of the main/parent.

To be blunt, while you can certainly have "generational" tables (parent, child, grandchild), I do not think that as you have described it, you CAN have multiple parent tables in this situation.

Having said that, my statement should be re-interpreted. If, in the table where this tag number is the PRIME key, you find that no record corresponds to this tag in any table where the tag is a FOREIGN key, it must be that the item in question is returned and no longer extant. In which the tag number entry is a place-holder for recordkeeping only.

UNLESS.... you have actually succeeded in defining a device table in such a way that for some devices you have no requirement for supplemental data. And THAT is desirable. Sometimes words are tricky and we see things in those words that aren't what we meant. Language does that to you...

With parent/child structures, you must ALWAYS enter data to the main table first if you wish to enforce referential integrity. So there is no real choice about data entry order. Main first, class-descriptor tables second. (Or parent first, child second.)

The issue about consumables is this: I imagine when you do maintenance on a device that involves pressure meters, pneumatic or hydraulic components, or the like, you will need to replace items like washers, bushings, o-rings, seals, etc. These are consumables. You might need to remove something and clean it. You would use an industrial disposable thing like a Kem-Wipe or whatever. Maybe you wouldn't track these to the specific number of consumed wipes, OK, but that's an example of a consumable. Small tubes of lubricants are another example. I've seen cleaning kits that have single-use tubes of lube, right-sized for the job of cleaning one instrument. The kit would be a consumable. If the same kit were applicable to, say, a UV-range photomultiplier and a visible-range photomultiplier, similar devices but probably in different instruments, then yes, the same consumable might apply to more than one device type.

How do you control what ends up in a child table?

By NEVER allowing a person to enter data through a query. In a form, you can put as much smarts as you need to demand proper data for the device in a single sitting. Define the parent record (which, because of RI, you MUST do first anyway). Once the form knows what kind of device it will be (perhaps through a list box or combo box), have the form CHOOSE which sub-form to open in a sub-form control. Bind each possible sub-form to the specific child table. If you link the parent and sub correctly (including the parent/child link fields of the sub-form control), data update of the parent and child will be synchronized. Then put some smarts in the sub-form to pop up message boxes when something is incomplete, impossible, or flat wrong. This is WAY down the road, I know, but you asked the question about the best way to do this.

Don't be sorry for questions. We all had to learn sometime. If you don't ask questions on THIS forum, you probably aren't paying attention anyway.

 

[wallis1905]

Yes, this is correct.

With parent/child structures, you must ALWAYS enter data to the main table first if you wish to enforce referential integrity

And just on this point. I now no longer need to use RI as Autonum is now the primary key with the foreign key being a long integer in the child table. The child tables no longer have a tagnum field. Anyway data will in practice always be entered into the main table first. And my device tables are defined by specnum. Some specnums do not have a child table.

And for the data entry, I'll never be able to use forms. Queries are the norm here for years. Maybe thats why there is a lot of errors! Anyway, as you say I'm a long way off yet.

 

[wallis1905]

UNLESS.... you have actually succeeded in defining a device table in such a way that for some devices you have no requirement for supplemental data. And THAT is desirable

Sorry, this is what I said was correct.

 

[neileg]

Been on the sick for a couple of days so haven't logged in. The training seems to be coming along nicely!

 

[The_Doc_Man]

for the data entry, I'll never be able to use forms. Queries are the norm here for years. Maybe thats why there is a lot of errors!

No "maybe" involved.

Ya know, there is still a way out of this dilemma...

What if it LOOKS like a query, SMELLS like a query, TASTES like a query, ...but is a form? Look into using what are called Continuous Tabular forms for your data entry. You can put VBA code underneat a form even if it is in Continous-Display mode. If it is a tabular form, it looks just like a spreadsheet or query in dataview. But code runs underneath it. Code that can perform validations. Code that can be bound to things on or under the form. Code that can step in and STOP THE MADNESS. (Sorry, Susan Powter...)

PLEASE treat this as me planting a seed, not telling you what you HAVE to do.

Also, ask around. Get opinions. Find out WHY you can't change from query entry. If none of the reasons are any better than "We've always done it this way" then screw them. (OK, they're your friends. You've got to work with them when you are done. So... screw them gently.)

"We've always done it this way" is why Man never invented airplanes, cars, trains, rockets to the moon, communications satellites, and computers. Oh, wait... we're talking by computer. That means that someone ELSE must have said "Rubbish. Time to do things differently." It happens. Maybe it can happen for you, too.

 

[boblarson]

No "maybe" involved.

Ya know, there is still a way out of this dilemma...

What if it LOOKS like a query, SMELLS like a query, TASTES like a query, ...but is a form? Look into using what are called Continuous Tabular forms for your data entry. You can put VBA code underneat a form even if it is in Continous-Display mode. If it is a tabular form, it looks just like a spreadsheet or query in dataview. But code runs underneath it.

You can also display the form as datasheet (which looks even more like a table/query) and still have all of the events available to you.

 

[wallis1905]

Ok dudes,
I'll check out your suggestions, sounds good. I'm off to Malta myself tomorrow so I'll be quiet for the week. Don't go away though I'll be back!

 

[wallis1905]

Ok Guys,
I really need time to digest all this properly. I certainly have improved my database structure drastically from with your help. But I'm sure as I study these posts i'll be able to make further improvements. Thanks a lot for the help.

 

[The_Doc_Man]

Good for you, wallis. Remember, it takes time. Sometimes it also takes a near inversion of your viewpoint to make progress. Here, sometimes it goes beyond thinking out of the box into thinking out of the tessaract.

One other bit of advice. Julius Caesar had it right. "Divide and conquer" is a valid approach to problem solving. Frequently you cannot fix everything all at once. You have to solve it a part at a time. But partial solutions that don't block subsequent work are a good form of progress.

Or, like her dad's advice in Contact, ... "Small steps, Ellie."