Suggestions to normalize a flat table 3

[montrose]

I have a flat tbl currently being used to store construction equipment data. I've gotten it to the point of being able to identify major equipment categories and sub-categories. I'm stumped on what to do with the specifications for the equipment that the user wants to be able to see. The unique attributes are a machine serial number and a user defined alphanumeric identifier. Example:

MANUF MODEL MAINCATEGORY SUBCATEGORY SN MACHNUM
Caterpillar D4c Dozer Standard 001 320CC

John Deere 450G Dozer LGP 2Z1 377

Kobelco SK200 Excavator LC 123 522

Grove RT58D Crane Crawler 111 002

Each of these machines (there are over 400) have specifictions that apply to many of the same MainCategory/Subcategory, but won't apply to other categories. The specifications are as diverse as Horse Power, Blade Width, Operating Weight, Main Valve, Grouser Pads, Air Conditioned Cabs, etc. They also can/cannot be fit with a number of different attachments (buckets, breaker balls, plows, etc.)

I've designed look up tbls for the Main Categories, the Subcategories, and used a yes/no field to ascertain if the equipment is an actual piece of equipment or an attachment to the equipment. But I'm not sure of the best structure to handle the specifications and which attachments go with which machine. Seems obvious I've got many to many relationships here but not sure how to proceed.

Does anyone have suggestions for me, or do you need additional info to offer assistance? Greatly appreciate any and all suggestions or examples of how someone has handled this in their designs. Learn what you can and share what you know.

 

[JimHorton]

You could have a separate 'MachineAttachments' table, say tblMachAtt, with key being the SN,MachineNum, and AttachementID. AttId links to a lookup table 'attachemnts', with key AttID.
Lookup table:
tblAtt:
AttID Desc
1 Plow
2 Bucket
3 Breaker Ball

tblMachAtt
SN MACHNUM ATTID
001 320cc 1
001 320cc 3
123 522 1
etc.

This way, you can have infinite attachements (without worrying about adding a new field for each new type of attachment. Records in the main table with no attachments simply have no records in tblMachAtt, and you outer join this table when used in forms.
--Jim

 

[JimHorton]

montrose,
I should have added, that to keep with your business rules of certain attachments for certain categories, you'd obviously want to write a validation function for the validation rule on the form (this would probably be to complex to do inline at the table-level) where you add/edit attachments.
--Jim

 

[montrose]

Jim, Thanks for the suggestions. The separate tbl for attachments and lookup certainly makes sense and allows the flexibility I need. Also appreciate the form level validation tip.

Any suggestions on the specificiations? I've managed to identify commonalities of cab type (open, closed, with or without airconditioning), engine horesepower, safety features (back-up alarms, fire extinguishers. But the rest! Measurements include inches, feet, yard, cubic yards of length, width, digging depth, boom length, etc. Some have certain types of valves/don't have certain types of valves which will/will not allow different machine configurations/capabilities. I've been thinking along the lines of look up tbls for measurements, type of measurement. One/many machines can have one/many specificiation and one/many specifications go with one/many machine. Kind of feel like I'm going around in circles trying to figure this out! And simple things like a key for a machine-the user has to charge the customer for a key if the key is not returned with the machine. I think I need a tbl to allow each machine's key multiple times to go out, be lost, get a new one, go out again. Appreciate ANY suggestions to normalize this stuff. Again, thanks for your initial good suggestions/tips. Montrose Learn what you can and share what you know.

 

[james33]

Dear Montrose,
Please realise that I do not mean to offend and I only offer the following as a suggestion.

One way to get a Database definition is to ask the users/operators, who may not have the knowledge of databases but who would certainly have a knowledge of the categories on a practical/subliminal level.
What goes with what is a practical definition, not an abstract concept which is why you are having a problem.
Designing a database has to conform to practical efficacy because practical people use them.
I sincerely believe that the biggest mistakes made by database designers/coders is when they don't ask, and then listen to the people they are designing them for, and then explain any anomalies/redundancies in a clear and concise way and then come to some agreement with the users.
I do realise that confusion sets in when you don't ask the 'right' questions so search for the general rules which do apply even with earth moving equipment.
I do think that you have to remove the most obtuse problems from the equation, i.e. a damages/shortages text field (to explain)+ a damages/shortages currency/number field (cost of) will take care of the lost key problem + any other eventualities otherwise you end up with as many fields as there are grains of sand on a beach.
If you treat people as fools they will only try to live up to your expectations, remember they have been doing their job quite well before relational database rules ever leapt out of Codds underpants and they will put 00000 or 999999 (as database designers did themselves) in any fields they don't see a need for.
One can't cover every eventuality, I can see that you are being very thorough but maybe it would be useful to ask the users what questions they will wish to ask of their database and then work backwards from there.
I do hope you will accept, reject or ignore anything you feel you can/can't agree with, I only offer this as a philosophical argument from a practical person's point of view and I wish you all the best.
Regards Jim

 

[montrose]

"...relational database rules ever leapt out of Codds underpants..." (an exceptional phrase!)



Jim, Thank you for detailing your philosophical argument. For the sake of brevity, I did not detail how this flat table came to be. It was, in fact, derived from the users who reached a consensus on what information they want from "their" database"; who are very aware of ineffiency from lack of data availability; and have no additional suggestions for the specifications or grouping of the specifications except "we need them". I am "working backwards" and forwards to give them what they need in a more efficient design than what they got the first time to improve their data. Sometimes philosophy obscures reality and the practical. Your suggestions for improvement are welcome. Kind regards, Montrose






Learn what you can and share what you know.

 

[JimHorton]

montrose,
I've got a system that worked for me in a db where the client had over 1000 parts that were needed to be listed for each manufactured item, of course each having differing parts. It's similar to the aforementioned attachment design, I'll post that later, the db is at home, and I'm toiling away...for at least half the day.

But I'll temper james33's suggestions a bit. Yes it's true that user input/interaction is very important, and I believe that's what sets some developers ahead of others. Yet if we're talking about, say, order-entry users, their type if input is more useful at the user-interface level--I'm not going to ask them how to design the db, however if they say it'd be nice to see a component listing for the machines, then yes, you take that into consideration.
However the core design, that's a meeting with managment--whether it's finacial, operations, manufacturing, whatever--they themselves may never see a screen of yours, only some paper reports handed to them by an assistant who uses your db, but they're the one who'll decide what data to store, track, summarize, etc, and you decide how to electronically achieve that end, how to electronically describe some set of real-world events or actualities. Just don't get caught in the trap of catering to the user's every wish-list item, that causes scope-creep and unecessary complexity.
I'll post the info on that design later...
--Jim

 

[montrose]

Jim, Thanks again for your input. From james33's suggestions which said, "...you have to remove the most obtuse problems from the equation, i.e. a damages/shortages text field (to explain)+ a damages/shortages currency/number field (cost of) will take care of the lost key problem + any other eventualities otherwise you end up with as many fields as there are grains of sand on a beach", to your comments, it's the complexity level I'm primarily concerned with at this time. Management wants the users to have access to data without having to dig through pounds of specifications books while their customer may be sitting on hold-some customers call in and ask for an "excavator" and the users have to patiently work with the customer to determine the specific machine they really need. Some of the users think their big flat table would be ideal as is. I know they could care less if the data is normalized! So the users need info and management wants efficiency and cost savings (key replacement adds up over time-not just the cost of the key but the worker timer involved)). The quest for an artful blend of data, technology and user satisfaction (data input, sales, service, management) based on the business rules finds me seeking suggestions. I look forward to seeing your design whenever you get time to post it.

Most grateful for yours, james33, and any other comments or suggestions. I, thankfully, get to take off for a few days of much needed holiday time where I'm going to worry more about knocking the skin off a little white ball than the complexities of this earth moving equipment! Checking back on Saturday morning-Thanks! Montrose
Learn what you can and share what you know.

 

[james33]

Dear Montrose,
I do not envy you your task and admire you for the thought and consideration that you are putting into it.
I was a little/lot premature in what I said and I apologise unreservedly for sounding a wee bit pompous.
Regards Jim

 

[BoxHead]

Montrose,

It certainly sounds like an interesting project. I seriously hope you'll continue the thread to keep us up to date on your progress.

I had penned a rather long reply, but deleted it with the realization that the longer the replies are, the less likely additional contributors are to participate.

Believe it or not, this is the short version.

Your customer's customer is primarily concerned with two things: "Do you have a machine that can do the job I need done?" and "Is that machine available when I need it?"

The second question is just as important as the first so it seems that a transaction management table and form will be required to determine when equipment is reserved, when it is out and when it is due back. This would be linked with the customer table and could be an appropriate place to track lost/replaced keys, broken lights & windows, bounced checks, late returns and all of those other little incidentals that erode the bottom line.

The first question, "Do you have a machine that can do the job I need done?" shifts the focus and the priority to the 'Category' for each piece of equipment. Don't query through the accessories available for cranes to tell me if you have the equipment I need to trench and backfill a foundation. This being my inclination, I'd offer the following.

tblMachines
fields: MachineSerialNum, MachineID (key - the alphanumeric you mentioned), MfrName, MfrModel

tblCategories
fields: MachineID (fkey), 4WD_Loaders, ADTs, Backhoes, Crawler_Dozers, Crawler_Loaders, Excavators, Landscape_Loaders, Motor_Graders, etc... (snagged these from JohnDeere.com){These would all be yes/no fields}

and then, a Specification table and an Accessory table for each category with it's appropriate specs/accessories

tbl4WD_LoadersSpec
MachineID (fkey from tblCategories WHERE [4WD_Loaders] = Yes), LoaderSpec1, LoaderSpec2, ... LoaderSpec n

tbl4WD_LoadersAcc
MachineID (fkey from tblCategories WHERE [4WD_Loaders] = Yes), LoaderAccessory1, LoaderAccessory2, ... LoaderAccessory n

tblADTsSpec
MachineID(fkey from tblCategories WHERE [ADTs] = Yes), ADTsSpec1, ADTsSpec2, ... ADTsSpec n

tblADTsAcc
MachineID(fkey from tblCategories WHERE [ADTs] = Yes), ADTsAccessory1, ADTsAccessory2...

So on and so forth.

I realize it would be more traditional to put the categories in a table of their own, however, I used the categories as fields in a table because I think it will facilitate rebuilding the 'monster' flat file some of your clients are accustomed to. I'd imagine that the people happy with the flat file are the ones who are the most knowledgable. I think we have to recognize (as they may already) that the tool you are building will effectively devalue their knowledge as it 'de-skills' the job of answering the customers' questions. By the same token, your tool will negate the inexperience of other less knowledgable employees. And, as the managers of this company probably know, the ten-year outlook for qualified, intelligent, motivated people entering any aspect of the construction industry is extremely bleak.

Montrose, I wish you the best.




John

Use what you have,
Learn what you can,
Create what you need.

 

[JimHorton]

montrose,
Before using this suggestion, keep in mind that in many cases proper normalization will render this scheme unecessary. Hovever, this is offered as a way to get around the 255 field limit in access. I couldn't find my original .mdb, it was a few years ago and I'm going from memory here...

The general design is below. It's sort of like 'verticalizing' a standard datasheet, but in reality, it's just a list that you add intelligence to. When complete, a simple crosstab query can display this like a datasheet (Use First instead of Sum, and ony group by fieldname). Keep in mind that there is a significant amount of extra validation formatting and other miscellaneous peripheral work that needs to be in place for the integrity of the data to be as strong as possible, and to make data entry/edit smooth.

I'll use generic table names.

tblMainProduct
MainProdNo LongInt --Key
ProdDesc Text()100

tblPartFields
MainProdNo --Key
FieldName Text(15)--Key
FieldComment Text(50) 'comment or description, or caption for Field
FieldValue Text(100) 'stadard datatypes can all be cast to text
DataType Text(10) (can use VBA type Number if desired)

However, the key difference in using this an a normalized 'Attachment' table as I showed way up above, is that in the attachment table above, you are restricted to 'attachments', of a particular set in a lookup table, and of a particular data type. In this sheme, you dynamically create the 'Fields', and give it a datatype (this is where validation comes in). For example, say you wanted an array of fields, say, BaseComponents, of which in a certain machine there were 300. You could name them Part1, Part2, Partn, etc, make the 'datatype' Long, use a lookup in a parts table. You cold a more descriptive comment *for the field, not the value* in the comment field. Then you could have dateInstalled fields, say for a machine that was put together over weeks or months: InstallPart1, InstallPart2, InstallPartn, etc, and make the 'datatype', Date. The next machine may have 600 parts, the next may only have 50. Each part has it's own set of fields, and default 'sets' of fields can be defined and loaded, but you are never limited to the amount of 'fields' used.

Note that the fieldValue is *always* text. This is the only way to do this when the fields are 'verticalized' like this. You must have a validation function that you call whenever data in entered or edited. This must look at the datatype, and you push the value through a mask or other appropriate validation techniques to validate not only the data type but the value as well.

As I said above, you can display via crosstab or just display vertically in a list. Simple SQL is of course not available, ie. you couldn't say "Select * from tblMainProduct where Part5 = 999". You'd have to say "Select MainProdNo,ProdDesc, FieldValue from tblPartFields Inner Join tblMainProduct on (tblPartFields.MainProdNo = tblMainProduct.MainProdNo) where Fieldname = 'Part5' AND FieldValue = '999'"

There is much more that is involved, but this is the basics. Again--look at ways to normalize first, often the solution lies there. But this can fill the bill in the odd circumstance where you may need it.
--Jim

 

[montrose]

Jim (James33)-No apology needed-better to say something than to have someone miss the boat-they will always pay later! You gave some good suggestions and helped me re-look at my process and the general rules that apply to this equipment-a great re-starting point.

John (Boxhead)-Seems like you've been sitting beside me as I've observed the flow of info within the company! These are some of the EXACT questions I've heard/asked (as well as the fear of being 'de-skilled') and have indeed been the basis for some of the design decisions made. The user was pressuring me to use their alphanueric coding as the basis for anything related to the equipment until I pointed out that doing so meant searching through too many records.

Jim (JimHorton)-A VERY interesting and detailed suggestion. Some of your date descriptors especially have been rolling around in the back of my mind as I watched folks having to call the Service Manager to find out when tires had been installed on a particular machine-that should be info at their fingertips. Some of the less knowledgeable/skilled individuals will benefit from seeing a vertical list view of specs. As I've played around with this I realized (and you validated) that a simple sql won't cut it.

I'm going to mull all this over and see what I come up with as an end result. One of my thoughts over the last few days (obsessed me even as I whacked at that golf ball!) was further overall classification of the specs into generalized groupings. There seem to be commonalities in descriptive specs, design specs, technical specs, operational specs, assembly specs. Whether that works or not I'm not sure.

If anyone else has a another 'light bulb' that goes off about this, please let me know. I have gotten nothing but outstanding feedback and suggestions, and will throw out the fianl design for you to chew on soon. Tried to give everyone stars for this, but I keep getting an error message when trying-will let Tek-Tips know. Please know I give you stars * * Thanks so much, everyone for your well thought out suggestions- Montrose

Learn what you can and share what you know.

 

[JerryDennison]

It's not excessively difficult to normalize a flat file db. In many ways it's actually easier because the groups are usually very readily apparent. As for the "old" users, if the interface is similar in appearance to what they're used to they need never know you've normalized it.

 

[montrose]

Jerry,

My intention is definitely to have the users not realize the data has been normalized. Improved and more comprehensive, perhaps. Thanks for your suggestions/input. Learn what you can and share what you know.