Database Planning

[wallis1905]

Whooah!

Ok Chief,
I'm going to need a bit of time to digest this. As you may have gathered I'm pretty new to all this and am definetly not qualified to perform the task, so i'm struggling a bit. I'm sure I'll get there.
Thanks again lads.

 

[wallis1905]

Tables

And about the tables. I guess my statement is due to working with a badly managed database prior to this. And the more I learn the more I realise that it was in fact not managed at all. All users can access all tables at present and do what they want. This meant that the guy who was say responsible for Flowmeters, had his own table which he filled by copy and pasting from a filtered set of the main table. He then filled in the rest of the fields himself(The unique and semi unique ones). When finished, he would run a report that drew data from this flowmeter table and the main table. These repot sheets are then used to order the instrument.
So what I mean is, everybody is used to having "their table", and they can understand where there data is coming from easily. I'm basically trying to improve this system without changing the structure too drastcally. The fear is that people will not understand how the system works and slowly tear it apart baqck to the way it is now! Which has happened before. Maybe we do need security!

 

[wallis1905]

Two way referential integrity?

And sorry, but what do you mean by two way referential integrity? Can it be any other way? I mean I set up a relationship and i tick the box is there another way? Or do you mean something completely different?
And also, If I do not tick the box for cascading deletions does this not mean that I can delete from any table, without effecting an occurance of tagnum in another table? Note tagnums will never be deleted from the main table. Instead they are RFS (Removed From Scope)These letters are put in a tagnums "scope" field so that all queries used, for reporting etc filter out all RFS'd records.

And just to be sure neileg, when you say attribute I presume you mean field? I'm going to have a think about what you said too. I'm not sure I quite understand. When you say that "in your multitable set up this would mean four tables" are you saying the muti table set up I currently have?
And by unit do you meam engineering unit mm,Kg etc? And if so why then do you not need two fields in each of the four tables to store the unit? Am I confused?

 

[The_Doc_Man]

One-way referential integrity allows you to delete a child record without deleting the parent record in cascade. But it requires a one//many mapping. When you have one//one mapping from B (sub) to A (main), Access really doesn't know OR CARE which is main and which isn't. Where you have the problem is if there is ALSO a one//one from A (main) to C (sub different from B) AND you delete a tag number from B that deletes the entry from A because of one//one RI, and C happens to have that same tag number. So when the reference in A goes away, so does the one in C, even though the whole thing started from B. This one//one mapping with RI is a two-way RI because of this ability to cascade delete the world.

Neil and I have both used this method in the past for different things. You have such complicated tables that you have too many fields to hold them all and that is because (as near as I can tell) you have no uniformity in the type of data you keep for each. Your solution has already gotten so unwieldy that you are calling out for help and finding that we are daunted by the situation.

When we use an ATTRIBUTE table, it is a procedure not unlike defining a set of properties of an arbitrary nature. The attributes might be like fields in a sense, or like properties, descriptions, or values in other senses. It requires you to break the normal binding between a thing and its description. You define the thing in its basic, most common terms (your MAIN table) and then make essentially a LIST of other attributes. The list includes the Tag Number so you know to what the attribute applies. It also contains the information needed to DEFINE the attribute - on the fly, as it were.

In a static table, you have to predefine the fields. When you have enough non-commensurate, non-overlapping attributes, you get a gazillion fields and 40+ tables. But if you make the mental break, then use the list to ... well, ... LIST the attributes AND VALUES of the the items, the list can be of arbitrary size. You dont' have so many fields or wild tables any more. But it requires you to step away and step WAY outside the box

 

[wallis1905]

Yes thats what I'm afraid of, I'm not sure I'll be allowed to change the structure so drastically. I'll try to read and understand though. Thanks a lot.

 

[wallis1905]

And sorry, but I hate to disagree.
I've just connected my main tabe to two secondary tables via a relationship (one to one in each case)
I have enforced RI, and cascade updates but not deletions. The join type in both cases was "All records from main table and...etc".
Now I can delete a record from either secondary table and not effect the other secondary table or the main table.
However I cannot delete a tagnum from the main table if it's in any of the secondary tables. Which is actually perfect for what we do here. Tagnums should never be deleted, but instead are RFS'd an excluded from view via filters. Records should only be deleted from secondary tables.
Now I believe that these relationships work like this only if the secondary tables are the "Related Table/Query" in the relationship dialog box. ie When making the relationship, first select the main table tagnum and then drag to the seconday table. This controls the direction you cascade in No?


However, Tagnum changes now become problem as changes in the secondary tables tagnums are not updated to any other tables. So if you do change one here, its link with the main table becomes broken as the tagnums now no longer match and changes coming from the main table will not update to that secondary table.
I think this would not be a problem if I could block users from editing tagnums in the secondary tables. Infact, all of my proposed data entry Queries look at both the main table and a secondary table (Join type "All records from main table...etc and and a filter on spec number and RFS). This means that althoug a secondary table contains a bogus "duplicate" record it will never even be seen by the report anyway (Once the specnumber has been corrected in the main table. The tagnum seen is Always from the main table, meaning that if users can no longer access their tables directly, and just use these data entry queries I'm safe?? (in this regard at least). Maybe allowing them see their tables as read only if they really wanted. I presume this is possible?
Sorry to fight your suggestions on this but I won't be permitted to tear the thing appart and completely restructure, for sure.

 

[The_Doc_Man]

And sorry, but I hate to disagree.

Dont' be sorry. Just keep your eyes open. Trust me, it ain't the first time I've offered an opinion and been told "that dog won't hunt."

I have enforced RI, and cascade updates but not deletions.

That's how you prevent a child-table deletion from killing that record in every other table. But if you had turned on deletions, too, you would have seen the vicious cascade I mentioned.

This controls the direction you cascade in No?

No. Not in a one//one case. In a one//many case, you would delete from parent to child regardless of which way you drew the relationships because Access sees it as a symmetric relationship. In a one//one, life ain't so simple.

However, Tagnum changes now become problem as changes in the secondary tables tagnums are not updated to any other tables.

A side effect of improper normalization, probably. This is because you are trying to be loose with structure and then ask Access to tighten it up for you. Your basic table design is your problem.

I'm not sure I'll be allowed to change the structure so drastically

Then think of your current structure as job security. Unfortunately, it will also be an albatross (as in Rime of the Ancient Mariner, a weight forever around your neck.) I can see it all 40 years from now, you'll be watching the movie Rocky XLVII and get a phone call. And the first thing the caller will say is "Wallis, do you remember that database you did for us in 2007? Well, ..."

I think this would not be a problem if I could block users from editing tagnums in the secondary tables.

This is WHY we told you earlier not to publish anything but forms. When an end user with no database savvy can see raw data, it is like boll weevils in a cotton patch. You have no input control over your data until you lock it all down into forms.

Sorry to fight your suggestions on this but I won't be permitted to tear the thing appart and completely restructure, for sure.

Wallis, it makes me no difference if you sh|t-can every suggestion you've seen here. It's your job, your performance review, your feeling of self satisfaction at a good accomplishment. And you AREN'T fighting anything I've said. From your description, the fight is with Access because of a really poor data design. But I'll make a suggestion along those lines. Recover this thread and print it. Take it to your boss. Say, "Look, the advice I'm getting here says our structure needs to be reworked. Really seriously reworked. The longer we wait, the more this is going to cost us in maintenane down the road. It is a case of pay me now, pay me enought to retire on later. so... what do we do?"

Every manager I've ever seen understands maintenance costs. The cost of your current design is going to spiral.

But as to where the fight REALLY lies, I'm flat serious. It is your call, not mine, because it is your database, not mine. You are getting free advice. It is never worth LESS than you paid for it, and might not be worth MORE, either, if you can't make it work for you. That's entirely your call.

I won't cut you off from advice. But I'm telling you right now, your design is causing you to p|ss money down a deep, dark, smelly hole, and when you are done with thing (if you ever are), you won't be flushed with pride. You will be relieved to be able to dump it. If your company will ever allow that. After all, who else will know the design like you do? Who else will understand how it all works - like you do? Who will ever want to take this beast over from you? Who will be your replacement so you can be promoted? (And if you can't make it work better, why would they want to promote you?)

If this sounds harsh, I guess I should apologize. But I'm telling you now, you are going to find help harder to get from here the farther away from normalization your database gets. Sometimes a dose of reality staring you in the face is the only way to convince you of the severity of the problem.

 

[boblarson]

And I will "second" EVERYTHING Doc_Man wrote.

 

[The_Doc_Man]

Thanks for the support, Bob. I really hate to have to switch to "tough love" mode, but sometimes it is required.

 

[boblarson]

I've been in that same situation years ago - either fix it now or suffer with "band-aid" fixes for the life of the database. In the first case I didn't fix it and wound up hating my decision immensely. The next time I ran across something that needed fixing, even though it took a LOT of work (real major overhaul), it was so worth it.

 

[wallis1905]

Ok so it's a very annoying but secure job or a total overhaul. I guess As you say, I should show my boss and leave him make the decision.
Can I ask one more question. When I told my boss initially that we should follow the normalization rules he asked me what that was. I told him in a hand waving way that it meant storing each occurence of data just once in the database and using references to link fields to theses tables of data. (Correct?)
His reply was that he felt this would take to much rework to the database at the start of each project. A project typically takes a year, after which time these data tables would all have to be changed for the next project to start up. Does this make sence in practise? I don't think so to be honest because you can still enter something once rather that 4000 times? Excuse if I'm not making sense here as I still need to sit down with pen and paper and think how it could be normalized.

 

[boblarson]

Actually, with a properly normalized and designed database you don't need to redesign anything at the start of each project. You just keep adding projects and data. The main thing is to figure out how you can make it able to take information for various projects, even though some of the data stored may be different for each.

 

[Wiz47]

I've been in that same situation years ago - either fix it now or suffer with "band-aid" fixes for the life of the database. In the first case I didn't fix it and wound up hating my decision immensely. The next time I ran across something that needed fixing, even though it took a LOT of work (real major overhaul), it was so worth it.

I had to do this at work not long ago (that's what got me back into Access initially) The employees had been entering data directly into tables for so long that they were VERY resistive to using a form instead. But, being the manager, I was able to force that on them despite their protestations. Now, a few months later, they couldn't be happier and never want to go back to tables again. I'm happier too because I can control the data entry - and that means far fewer mistakes or inadvertent corruption. Like you said, it's a matter of pay me now or pay me later

 

[The_Doc_Man]

Wiz, your saying at the bottom of your post CONVINCES me you have at least SOME Cajun in your blood.

Back to Wallis

Normalization is not hand-waving or magic. In a simplified sense, you normalize a database to "purify" your tables. Let's see if I can do this REALLY simple for your boss, who probably needs it.

You normalize to assure that your apples table only has apples and your oranges table only has oranges.

You normalize to assure that each row of the table describes only one piece of fruit at a time.

You normalize to assure that you will remember what each type of fruit is good for even if you happen to run out of one for a while.

You normalize to assure that you NEVER EVER have to guess where to find data on apples - or oranges - or lychee - or pomegranates.

You normalize so that it becomes trivial to add more data in an organized, rapid, predictable way.

Normalization is merely the elimination of ...

1. ... extra copies of something large that is fully defined elsewhere.

2. ... stuffing multiple items in places designed for single items.

3. ... misfiling. (When you normalize a table PROPERLY, you will know the moment you try - or before you try - that you are storing data in the wrong place - if that is what you are doing.)

4. ... improper mingling of items that should be at different levels of your business.

Normalization also improves speed in many cases, improves the total amount of storage in all cases, and in general provides for far better maintenance.

His reply was that he felt this would take to much rework to the database at the start of each project.

HEY MISTER BOSS!!!!!! HOW ABOUT A PROJECT THAT TAKES FAR LESS TIME TO SET UP IN THE FIRST PLACE? IS THAT WORTH SOME EFFORT?

 

[neileg]

Wallis, I understand your frustration. I came to Access a few years ago because I had a department awash with data in spreadsheets. I had done some database development before but not in Access. So I had to dive in and learn. I wince, now whe I look at some of the databases I developed back then (even though some are still in productive use). I learned the hard way that if you don't get it right at the start, it'l never be right. Every thing I know, I learned from this forum, that's why I still log in, even though I haven't produced a database in two years.

What I can do now is talk to our professional(?) database developers with some authority, and I know how things should be done. When I find that someone has made a description field a primary key so it can't be changed, I know it's bad design and I don't get fobbed off!

 

[wallis1905]

neileg, an you tell me what you mean about the primary key being a descriptive field and why it's a bad idea. By descriptive I mean it's a string made up of usually six pairs os characters. EG first pair = building num, 2nd pair = room num, 3rd pair = Instrument type....etc. What makes a good primary key?
And for the rest of you, I'll tell you how my fight goes tomorrow!

 

[neileg]

What makes a good primary key? Autonumber does it every time for me. There's a debat in one of the forums going about meaningful/meaningless PKs. But I prefer to leave Access and its autonumbers to do the referencing. Anything with meaning attached to it I would hold separately. And where subsets of the field have meaning as you describe, I would hold in separate fields and concatenate them for display only.

 

[wallis1905]

Ok,
So, if your autonum is the primary key can you tell me what this means regarding a normalized database? I presume the autonum is only in one table, and all relationships are via other fields.
Am I trying to plan out in my head how I could fit my database into the normalization model.
I have been looking at the Northwind sample database that vomes with access. I presume I am looking at a normalized database here?
In the northwind database there is about 8 tables or something. For example, one is called Employees which contains all the info about each employee. Now this works great when you have a lot of fields dependant on one (ie Employee_ID) field. However, In my database I can think of lots of examples of fields that do not particularly depend on any others. What do you do with these. Just make a single field table? If so, then I'm sure I will end up with a lot more than the original 40 table I had. I really dont see how so many seperate tables could be easy to maintain? Am I missing the point?

 

[The_Doc_Man]

With regard to key structure...

it's a string made up of usually six pairs os characters. EG first pair = building num, 2nd pair = room num, 3rd pair = Instrument type....etc. What makes a good primary key?

Well, this is subjective, of course, but.... what you just described is NOT a good key precisely because (A) it has multiple components and (B) the individual components have separate and independent meaning.

This is probably going to be a case where an autonumbered key is your better choice. The problem with synthesized keys (i.e. made as a composite of other meaningful data) is that you either have duplicated data or an impossibly complex compound key.

In your case, that composite key has meaning. (You said so.) So... suppose some day that you get a request for a report for only those devices of a particular type in a particular building, showing room number. How will your database support that query? OK, the rule is that Access can't tell you anything you didn't tell it first. So to search for the devices, they must exist in a way that you can QUICKLY find them. Ditto, particular buildings.

If this is a single-field key, you have to either break it apart to do the search, which adds a lot of time to the operation. Or you have to have those same elements a second time as individual fields to support a query.

If on the other hand they are already separated, then this is a compound key with six elements. OK, Access allows 10 elements in an index, so you can do this, but you are over 50% of the complexity of the most complex key allowed. It will take you AGES to build proper keys for this table. Not to mention that I'm sure at least a couple of those key elements are merely used as "discriminators" just in case you ever have two of the same type of device from the same manufacturer in the same room in the same building.

Use this concept instead...

table INSTRUMENTS
InstID, PK, autonumber (long)
Bldg, number or code (in the code case, FK to a building table)
Room, number or alphanumeric (depends on your room numbering scheme)
DeviceType, text or code (FK to a device type table)
DeviceMaker, text or code (FK to a device supplier table)
other device data...

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.

However, In my database I can think of lots of examples of fields that do not particularly depend on any others.

If this is true then they don't belong in the database. Or you have incorrectly estimated their dependency. But I'm glad you expressed it as you did. That fact means you at least partially understand one of the aspects of normalization. No element belongs in a table unless it depends on the entire key value and nothing else.

Your earlier question "What makes a good key?" is answered simply. If it is possible to find any key that satisfies this dependency requirement, then it is a good key. If you have to force the issue (have a "discriminator" element to avoid duplicates built from your meaningful key elements) then it is NOT a good key.

I used an earlier example regarding candidate keys. A USA Social Security Number is supposedly unique (excluding identity theft cases). A company could use an employee number or the SSN as a key in a personnel table. Either one would be enough to guarantee uniqueness. External reasons would suggest use of the Employee Number in preference to SSN. But either would be a good key. Both of these are NATURAL keys because both have meaning.

In your case, there is probably no single unique NATURAL key because you have different instrument serial numbers (or maybe some with no serial numbers), different makers, different types, different locations (bldg+room), different model numbers, etc. etc. etc. In this case, you would probably do better (and take up less room) with a GENERATED key (autonumber). This is because taken by themselves, all of your candidate NATURAL keys would fail on grounds of uniqueness, and even a combination would fail without the addition of that discriminator field (to handle duplicate devices in the same room). Therefore, you have NO apparent natural keys.

I think a large part of your problem is that your business model is giving you trouble here. And that is crucial to your success in taming this beast. Do you understand how to determine the nature of the tables you really need? With no disrespect intended, I think you don't - because it sounds like you let some non-tech people define some or all of the 40 tables you mentioned.

 

[wallis1905]

Ok Doc Man,
Thanks again. Yes you are correct about discriminating between tagnums. The last two characters are numeric. So if the same type of instrument is in the same place then they can still be unique. So yes I understand that I can make the autonumber the primary key and breaking up the tag for filtering. If the Autonum is PK do you foresee it being a part of a relationship with another table? Sorry I don't know if I need to have a "main table" in this database?

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."

I'm confused by this. In my mind this is what my current structure is. I have a table for each "type" of instrument. In fact the type in the tagnum is not specific enough so we base the tables on spec_no, which is not in the tag. (Not that it matters if it is or not?). These tables are currently connected by a relationship to my "main table" via the field tagnum. And these Sub tables have lookup tables in turn, containing any data that is totally dependant on another field. ie when you select a certain Am I missing something here?

Quote
"If this is true then they don't belong in the database"
But what do I do with it? For example I have a range for most instruments. EG Pressure transmitter 0 - 5 BarG. The only thing the range can be considered to depend on is Tagnum. Does this qualify as a dependancy in your eyes?

Quote
Do you understand how to determine the nature of the tables you really need? With no disrespect intended, I think you don't - because it sounds like you let some non-tech people define some or all of the 40 tables you mentioned.

Yes your right I don't! And I think the reason I don't, is that there are so many fields to be considered, and so many of them occur so infrequently, and so many seem to be related only to tagnum (in the sense just discussed). I'm daunted by this task.