quantity field in a single record & normalization

[Bobfmf]

Preface: this issue could affect relationship design, so I put it here, but I *think* the core issue is normalization.

Tool_table is an inventory of gardening tools. Most of the the records have only one tool in them, so rake A has a record separate from rake B. List_table's purpose is to generate a list of tools that a single user will use for a given day (there are no other users, so there is no issue of double-booking a tool). The user will have some mechanism for choosing the appropriate tools, and be able to save the output for future work (improving the efficiency of the lists all the time).

When building the day list of tools, I would also like to be able to just list some quantity of a tool, like stakes. So the user would see that he will be using some actual tools, each listed separately, but also 25 stakes.

If, in Tool_table I have a single record listing "stake" as the item, and then have a quantity field (listing, say, 100 stakes), is this a poor design (the use of multiple quanties for one record, that is)? Perhaps having a quantity field isn't necessarily a bad idea, as long as I'm not trying to relate the quantity in the Tool_table (100) to the List_table's quantity field (where the user only needs 25 for the day). Thanks.

Bob

[Razumovsky]

Initial thought:

I would add 'Project' and "ProjectTools' tables. If I understand correctly, it seems your goal is to reuse the info by Project, not by day... your quantity field would be in the ProjectTools table.

HTH

-Raz

[Bobfmf]

> it seems your goal is to reuse the info by Project, not by day...

Although I do want to know the date, "Project" is better description. I want to be able to review past projects, see which tools (and quantities) weren't needed that were on the list, as well which were not on the list but were needed.

> your quantity field would be in the ProjectTools table.

This is where I'm having difficulting thinking through the design. Right now I have a Tools table, and I have a separate Word documents with: item, quantity, and a notes container that I use to refer to actual items (e.g., QTY: 3; ITEM: rake; NOTES: bamboo, metal, mini). This clearly is not a good database design -- it works well as an ad-hoc system that a human can easily interpret, but it fails in that it isn't systematic nor re-usable and therefore doesn't achieve my ideas of drawing from a Tools table (after I build the functionality in the Word docs into FM).

You suggest having the quantity field in the Tools table -- what if I have a record in that table that says QTY: 100 ITEM: metal stake, but only want to use 25 for a project?

I realize part of the difficulty for me here is that my model is still fuzzy; I appreciate being able to talk about it on this forum in this state. Thanks.

Bob

Edited March 7, 2006 by Guest

[Razumovsky]

Bob,

Think of it like a standard Inventory/invoicing design.

Inventory=Tools

Invoice=Project

InvoiceLineItems=ProjectTools

Your 100 number would be in Tools. This is your inventory (and as tools are returned after the project and you dont double book, you dont really need to add or subtract from this number based on projects)

your 25 number would be the quantity of a specific tool (ProjectTools) used for a project. This is analgous to the quantity of a certain item on an invoice.

Is that any clearer?

-Raz

[Bobfmf]

OK, I see you're talking about a quantity field in both projects and tools. That definitely makes it more clear. I'll work with this concept. Thanks!

Bob