Table redesign question 1

[TCARPENTER]

I have a database consisting of 4 tables:
[ul]
[li]Locations[/li]
[li]SpecialTools[/li]
[li]CommonTools[/li]
[li]Miscellaneous[/li]
[/ul]

Each table has a corresponding detail table which joins say Special Tools with the Locations table. Now the boss wants to add an operations table. Here's the rub: Not only are operations tied to locations; special tools, common tools and miscellaneous items are tied to Locations and Operations, the tougher part is they don't always know which one will go where, so they need the flexiblity to add items to locations and/or operations, and they want to be able to move any/all special tools, common tools, and miscellaneous items based on their operation to a new location.

Is this as simple as adding the Operations table, OperationsDetail table, and then an operations_recnum field to each detail table? My forms currently are based on locations, will I now need two sets of forms?

Any advice is greatly appreciated.
Thanks
Todd

 

[RickSpr]

I would need to understand your "tied to" relationships better to answer this. Try to explain with statements like the following:
One Operation occurs at one or more Locations.
One Operation uses one or more SpecialTools.
One CommonTool is used by zero or more Operations.
One Miscellaneous is stored in one or more Locations.

What's really important is the numbers. The actual italicized parts aren't really crucial, although they would help me understand more easily, and if you work them out you may sometimes find that there are multiple relationships between two tables that you're mentally thinking of as a single relationship. When there are multiple relationships, it's important to distinguish them.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[TCARPENTER]

Rick,

Thanks for the reply - in terms of relationships, its probably not going to get much uglier than what I've got:

[ul]
[li]One or more operations occur at one or more Locations.[/li]

[li]One operation can use one or more SpecialTool/CommonTool/Misc. Items.[/li]

[li]One SpecialTool/CommonTool/Misc. Items can be used by zero or more Operations.[/li]

[li]One SpecialTool/CommonTool/Misc. Items can be used by zero or more Locations.[/li]
[/ul]
Maybe an example will help:

Location: 001
Processes: Longeron attach, Longeron prep
Common Tools: Die grinder, Heli-Arc welder
Special Tools: Longeron locator, Longeron punch
Misc Items: Longeron Rack, Work Bench, Chair

Location: 002
Processes: Longeron inspect
Common Tools: None
Special Tools: X-ray scan
Misc Items: Laser Printer

Location: 003
Processes: Fuselage skin
Common Tools: Rivet Gun
Special Tools: None
Misc Items: None

The problem comes in when the project starts and the person entering data knows only bits and pieces of information but has to start populating the database, he or she needs the flexibility to enter what they know, and as the project progesses, they can then fill in the blanks.

My initial thoughts were to create an operations table with an operations detail table to associate operations with locations. Something like this:
tblCommonTools
CommonTools_Recnum - PK
CommonTools_Desc
tblCommonTools_Details
CommonToolsDetail_Recnum - PK
CommonTools_Recnum
Locations_Recnum
OperationDetails_Recnum
tblLocations
Locations_Recnum - PK
Locations_Desc
tblMisc
Misc_Recnum - PK
Misc_Desc
tblMisc_Details
MiscDet_Recnum - PK
Misc_Recnum
Locations_Recnum
OperationDetails_Recnum
tblOperation_Details
OperationDetails_Recnum - PK
Operation_Recnum
Locations_Recnum
tblOperations
Operations_Recnum - PK
Operations_Desc
tblSpecialTools
SpecialTools_Recnum - PK
SepcialTools_Desc
tblSpecialTools_Details
SpecialToolsDetails_Recnum - PK
SpecialTools_Recnum
Locations_Recnum
OperationDetails_Recnum

Hope this wasn't too long winded, and I hope I answered your questions after all of this rambling.

Thanks Again
Todd

 

[RickSpr]

I think there's one relationship missing. Correct me if this is wrong:
One Location can use zero or more SpecialTools/CommonTools/Misc. Items

In the final analysis, it appears to me that all your relationships are many-to-many. Your Details tables are the associative tables normally used to represent these relationships, with the addition that they combine 3 tables instead of just 2 (they represent many-to-many-to-many relationships).

That's about as flexible as you can get, and your thinking seems sound. I'll make a couple of notes though.

1. I usually name associative tables by concatenating the parent tables' names (in the singular, except for the last name). That helps me remember which tables they combine with, and actually makes sense in English too. Thus, I would name tblSpecialTools_Details as tblSpecialToolOperationLocations or tblOperationSpecialToolLocations. (The order of the names isn't important, but often one order seems better aligned with the customary way people talk in the business.)

2. You may or may not have use for additional associative tables that combine two tables at a time. You may think that they wouldn't represent anything you can't show in the 3-way associations, but it's more complicated than that. For instance, suppose a user knows that a tool is needed at a location, but doesn't know for what operation. Say you add it to the 3-way table, leaving the Operation_Recnum Null. Later, another user knows the same tool is used for an operation, but doesn't know the location, so you add that to the 3-way table with Location_Recnum Null. Finally, somebody with the whole picture comes along with all 3 pieces of data. But you now have 2 partial records in the 3-way table, so you need to update one and delete the other. That's going to complicate things and require code. If you had 2-way tables, the earlier updates would add to those, and the final update would insert into the 3-way table. It also means you wouldn't have any rows with Nulls in them to worry about. On the down side, it means you have a bunch more tables to keep updated. Relational Integrity (RI) and cascading deletes may help some, though.

Also, let me suggest that SpecialTools, CommonTools, and Miscellaneous might best be represented by a single table with a code column to say which kind of item is in each row. If these tables have identical columns, that is certainly the case. If they have different columns, it may still be simpler to have a table with ONLY the Recnum and code in it, and use outer joins to the other tables where necessary. It would reduce the table proliferation, which would big a BIG deal if you use the 2-way tables.

As I said, you have the right idea with your table design so far. Sadly, providing this degree of flexibility is going to take a lot of work, and probably no small amount of code. I really can't go further though, without more specific information about what combinations might be entered at what point. I also don't know anything about what information you need to get out of the database. From your description, I can only tell what gets input. With all these many-to-many relationships, this database is like a network that can be entered at any node to find out everything. That's where the flexibility comes from, but it also makes it hard to perceive the structure. Knowing the outputs might "nail down" one node as the start of things, making it easier to adopt a view and reducing the complexity for practical purposes.

Obviously, you're not the only long-winded person in this thread. I hope my verbosity is as helpful as yours was to me.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[TCARPENTER]

Rick,

You are correct I did miss the relationship you mentioned and your relationship scenario is correct.

I liked your suggestion in item number 1, your naming convention makes more sense and I can see how it would lend itself to making the database "self documenting."

You are dead on with number 2 and have really hit the crux of my dilemma, and quite frankly the company's. For many moons, they have had a host Excel spreadsheets, Access Databases, AS400's, and a couple of other systems all to track each person's responsibilities and needs. This is the first attempt to combine it all, so far the feedback has been really positive (after you past the initial "we don't need this" arguments, but who can blame them since everything before this was a huge unorganized, incomplete mess.) Because everything before was so incomplete, I'll maximize default values in the forms and tables, at least limiting the amount of nulls I'll have to address. Hopefully this plan will minimize the amount of code I'll need to write. By maximizing defaults, I have found, seems to make it easier on our users - they seem to like editing better than data entry.

The reports, as you might have imagined, are even more complicated than the input side of things, they want anything and everything from tool counts, to manufacturer's make and model numbers, to a complete report showing each location, the operations it contains, its parts, tools, etc., I think I've got even more work than even I initially thought.

Thanks for the great reply, anybody who can articulate as well as you can obviously knows his stuff, and your reply was more helpful than you know. When this thing finally starting gaining some acceptance, after 1-1/2 years, people are finally willing to sit down and give me some feedback. For a database cobbled together from Excel spreadsheets, Word reports and even some Power Point presentations, most feedback has just been little things, but this operations beast was a big miss. You've given me what I needed (and then some), I had some concerns about the relationships and knew I could handle it with code, but was a little unsure of the design, and wanted to be sure I wasn't overlooking something.

Thanks again Rick
Todd

 

[willir]

Todd

This looks familiar. I hope it is working out for you.

Using one "tool" table makes sense, and would simplify things. You can "sort" the common and special tools and the misc Items using a ToolType variable.

If you are tracking details on tools which differ from each other - for example, a hammer - weight and type, vs XRay scanner, you could use different tables. A "Tool Master" and then assoicated sub tables. This is messy in one way - you have to know which table to use to grab the detals, but it makes tracking the tools themselves much easier.

Richard

 

[TCARPENTER]

Richard,

Yep its the same one you helped me out on earlier, both the parts, containers, racks and the costing, it just keeps growing - which means they are pleased with what they've seen so far. At least the feedback has been very positive, and the floodgates seemed to have opened!

I liked Rick's idea of using one table but the actual Tools are far more complex and if you remember the other post I posted concerning parts, racks, and containers, this all part of the equation, along with costing information I had posted you helped me with. Eventually, they'll ask for costs based on operations - not just locations. So now my big issue is going to be redesigning my forms, I hit my nesting limit way back, and now I'm going to have to rethink most of it if not all of it.

Thanks for the input, I do appreciate it.
Todd