Hey! I'm new around here, an amateur access user, and a novice access/database developer. I'm trying to design a database and have a table design question:
I have a table [Values]. This table contains the individual dollar amounts that make up a whole contract. These individual amounts are broken out for tracking invoicing, and other things.
The [Values] table has a [ValueTypeID] field, related to a [ValueTypes] table. A "Value" can be of a type Contract, Quote Pending, Budget, Breakout, Work Order, etc. Depending on which type of value a "Value" item is it could have a number of different properties (fields).
For example, if a value is of the Breakout type it would have a foreign key field to refer another value. But if it were a Work Order type it would have a field for [Date Worked] (among a few others fields), and this information may not be relevant to the other value types.
What's the best way to approach organizing this data? I assumed that just creating an all the fields for all the possible "properties" a value type might have would bloat the table, many fields would never be used depending on the type. There is also the option of creating a different Value table for each type with its required fields, but that doesn't seem like normalization to me.
Thank you in advance for your time and any advice you can offer.