Help with database design

[Allyj]

Hi,
I am working on a small jewellery business database that I perform quotes on.

I update my precious metals price on a regular basis and in most circumstances I only need to use these current values.

However, I would like to know a way to store a historic metal price value without it updating when I change my prices.
It is useful in this scenario:

• When a customer has a quote that is 'out of date' to know what I have based the price on.
• Old Valuations that need updating to a current value for insurance purposes

Due to my inexperience with database coding. I am getting confused about the best way I might go about this.??

I hope this makes sense?
my quote form is based on a series of forms and subforms for each component
I have attached a relationship diagram to help better understand the structure.

Humbly appreciate any advice,
Ally

 

[The_Doc_Man]

However, I would like to know a way to store a historic metal price value without it updating when I change my prices.

Typically, you have a date in a price table for each price change. Then when you want to know the price of something, you have two choices.

The current price is merely price for the metal where the price record's "effective date" is the maximum.

The price at a given but not current time is the price of the metal where the price record's "effective date" is the maximum of all records for that metal but with an effective date less than the date of interest.

 

[ebs17]

I would say: new price => new record in a dependent table

Every price is historical at some point.

 

[Allyj]

Typically, you have a date in a price table for each price change. Then when you want to know the price of something, you have two choices.

The current price is merely price for the metal where the price record's "effective date" is the maximum.

The price at a given but not current time is the price of the metal where the price record's "effective date" is the maximum of all records for that metal but with an effective date less than the date of interest.

Thanks Doc_Man,
A couple of questions for you if you don't mind?
when you say :
The current price is merely price for the metal where the price record's "effective date" is the maximum.
Do you mean this is a table that stores multiple records along with their prices and a valid date on the same row and sorted by querying or searching for the date?
I was trying to avoid the table getting too large with what seemed largely like duplicate records with redundant data, but on reflection, I'm not sure it is redundant data now .
Still not quite understanding this.

Currently (this may not be conventional) I have a datasheet form that limits the records to my list of different alloys and I simply overwrite the [MetalPrice] and change the [ValidOn] date field which works well in most circumstances, until I discovered the need to store the old values.

I guess I was looking for a way to essentially copy the Metalprice value to a new field for example [HistoricPrice] that would be stored seperately but would not change once I update prices.

Thanks for you advice,
hope my reply makes sense,
Ally

 

[GaP42]

For the tables Gem, Contractor, findings and Metals you have a "DateValid" type field - if you did not update the record but merely copied down and adjusted the price then you have the history of prices. A "Trial Quote" type form could be used where you enter a TrialQuote date that is used to determine prices at that time for the assembly of the item. If you need to keep these trial quotes, then they could be added to the quote table - but you need to distinguish these from the real / submitted quotes. A quote type table would be appropriate.

 

[Pat Hartman]

I'm not sure how you handle material but I don't think the schema supports how I would handle it. You purchase stones, findings, and raw material. I don't know whether they need to be 1 table or three but for now, we can leave them at three. For stones and findings, you purchase by quantity but you also need material and weight (carats or ounces) as well as purchase date so you have some way of looking up the spot price for the day you bought something if you need to. For now, let's assume that findings are a single metal. Then you have raw material which might be scrap jewelry or actual raw material. This is purchased by weight. Prices of metals are quoted by ounce and for stones by carat, perhaps within a range since single large stones are priced at a higher carat weight than smaller stones. i.e. the price of a 10 carat diamond is more than 10 times the price of a single carat stone. When you buy stones in bulk, do you create a separate inventory item by cut and carat weight? So, a lot might have 30 stones of assorted cuts and carats. How does that end up in inventory?

So, you need to store the price you paid by ounce or by carat for a finding or a stone or raw material. But when it comes time to sell, you need to be able to translate the purchase price into the current price and you need to be able to look up in your material source database what the spot price is today for the metal or stone type. You can then calculate the current material value by multiplying weight by spot price, add in your overhead and markup to come up with the catalog price.

I'm pretty sure you've posted your database before and the price table was not normalized. I'm not sure you ever described your business practice so I'm not sure what might be the best way to help or how willing you are to change how you work. Lots of small businesses "never" do x and so you might say that you never purchase gemstones in bulk. OK, but you still need to record the price you paid for a bespoke stone. That isn't much different in concept. I also don't know if you ONLY use findings and NEVER build a piece from scratch. I would categorize chains as findings even though you sell chains unaltered.

The database seems to have been working for you for some time. So, please be clear as to what change you want to make to it or what long standing flaw are you trying to correct? What do you want to do that the current schema does not support?

 

[The_Doc_Man]

Thanks Doc_Man,
A couple of questions for you if you don't mind?
when you say :
The current price is merely price for the metal where the price record's "effective date" is the maximum.
Do you mean this is a table that stores multiple records along with their prices and a valid date on the same row and sorted by querying or searching for the date?
I was trying to avoid the table getting too large with what seemed largely like duplicate records with redundant data, but on reflection, I'm not sure it is redundant data now .
Still not quite understanding this.

Currently (this may not be conventional) I have a datasheet form that limits the records to my list of different alloys and I simply overwrite the [MetalPrice] and change the [ValidOn] date field which works well in most circumstances, until I discovered the need to store the old values.

I guess I was looking for a way to essentially copy the Metalprice value to a new field for example [HistoricPrice] that would be stored seperately but would not change once I update prices.

Thanks for you advice,
hope my reply makes sense,
Ally

When you have reason to keep historical prices, you need a way to document those prices. Stated another way, if you have to support multiple old prices, you need a place to KEEP multiple old prices. Here is a pared-down example of what I mean. You may require more than this.

Table MetalPriceHist
- field: MPHID - autonumber - used only so you can make external references to the record you want later, AND this is actually optional if there will never be a child table to this table.
- field: Metal - EITHER the name of the metal OR an ID code if you have another metals table and use some kind of code. I could see, for example, 24 ct gold as "Au24", 18 ct gold as "Au18", etc. - but whatever YOU use.
- field: MetalPrice - probably EITHER a Currency field OR a DOUBLE - the price per unit weight
- field: MetalUnits - either a code or an abbreviation for the units of that "unit weight" - ct for carat, oz for ounce, g for gram, Ist for imperial standard ton (only if you buy in bulk )
- field: PriceEffDate - the date in which that price went into effect.

Now build a query for current prices.

CurPrcQry:  SELECT Metal, MetalPrice, MetalUnits, Max( PriceEffDate) As EffDate FROM MetalPriceHist GROUP BY Metal ;

Then the current price for a given metal is selectable from that query (which is ALL current prices):

DLookup( "[MetalPrice]", "CurPrcQry", "[Metal]='" & [SelectedMetal] & "'" )

To do the trickier isolation to look up a price from a non-current date, you might have to do a little bit of recordset operation or a complex correlated query. Not sure which one of those would make you more comfortable.

 

[Allyj]

When you have reason to keep historical prices, you need a way to document those prices. Stated another way, if you have to support multiple old prices, you need a place to KEEP multiple old prices. Here is a pared-down example of what I mean. You may require more than this.

Table MetalPriceHist
- field: MPHID - autonumber - used only so you can make external references to the record you want later, AND this is actually optional if there will never be a child table to this table.
- field: Metal - EITHER the name of the metal OR an ID code if you have another metals table and use some kind of code. I could see, for example, 24 ct gold as "Au24", 18 ct gold as "Au18", etc. - but whatever YOU use.
- field: MetalPrice - probably EITHER a Currency field OR a DOUBLE - the price per unit weight
- field: MetalUnits - either a code or an abbreviation for the units of that "unit weight" - ct for carat, oz for ounce, g for gram, Ist for imperial standard ton (only if you buy in bulk )
- field: PriceEffDate - the date in which that price went into effect.

Now build a query for current prices.

CurPrcQry:  SELECT Metal, MetalPrice, MetalUnits, Max( PriceEffDate) As EffDate FROM MetalPriceHist GROUP BY Metal ;

Then the current price for a given metal is selectable from that query (which is ALL current prices):

DLookup( "[MetalPrice]", "CurPrcQry", "[Metal]='" & [SelectedMetal] & "'" )

To do the trickier isolation to look up a price from a non-current date, you might have to do a little bit of recordset operation or a complex correlated query. Not sure which one of those would make you more comfortable.

Thank you! I sort of understand what you mean now. I am away from my pc at the moment but I will delve into this later and see if I can get my head around it. Many thanks for your time.
Allison

 

[Allyj]

I'm not sure how you handle material but I don't think the schema supports how I would handle it. You purchase stones, findings, and raw material. I don't know whether they need to be 1 table or three but for now, we can leave them at three. For stones and findings, you purchase by quantity but you also need material and weight (carats or ounces) as well as purchase date so you have some way of looking up the spot price for the day you bought something if you need to. For now, let's assume that findings are a single metal. Then you have raw material which might be scrap jewelry or actual raw material. This is purchased by weight. Prices of metals are quoted by ounce and for stones by carat, perhaps within a range since single large stones are priced at a higher carat weight than smaller stones. i.e. the price of a 10 carat diamond is more than 10 times the price of a single carat stone. When you buy stones in bulk, do you create a separate inventory item by cut and carat weight? So, a lot might have 30 stones of assorted cuts and carats. How does that end up in inventory?

So, you need to store the price you paid by ounce or by carat for a finding or a stone or raw material. But when it comes time to sell, you need to be able to translate the purchase price into the current price and you need to be able to look up in your material source database what the spot price is today for the metal or stone type. You can then calculate the current material value by multiplying weight by spot price, add in your overhead and markup to come up with the catalog price.

I'm pretty sure you've posted your database before and the price table was not normalized. I'm not sure you ever described your business practice so I'm not sure what might be the best way to help or how willing you are to change how you work. Lots of small businesses "never" do x and so you might say that you never purchase gemstones in bulk. OK, but you still need to record the price you paid for a bespoke stone. That isn't much different in concept. I also don't know if you ONLY use findings and NEVER build a piece from scratch. I would categorize chains as findings even though you sell chains unaltered.

The database seems to have been working for you for some time. So, please be clear as to what change you want to make to it or what long standing flaw are you trying to correct? What do you want to do that the current schema does not support?

 

[Allyj]

Hi Pat,
How are you? Yes, you did help me end of last year and to be honest I totally scrapped my badly designed database and rebuilt it again a couple of weeks ago, taking on board some of your original advice on the structure. It works a lot better than previously but as a beginner there are always going to be things to learn.

I know there are always extra layers I could add, but honestly there is only myself and my partner who will use this at the moment. So I just want a simple tool that I don't want to be overly complicated. We don't but anything in bulk and we hold a lot of stock either. The functionality works well for the most part for what I want it to achieve.
It's just the original problem with prices and dates I need to solve really.
Thanks Allison

 

[Pat Hartman]

Did you understand my explanation of how prices need to be stored?
When you add the item to your inventory, you save the price you paid rather than the price in the Price table. You also save the type of material and weight. Then your pricing table only needs to have the most recent prices and depending on the volitivity you would update the price table daily or weekly. The price table should be:

tblPrice
PriceID (autonumber)
MaterialID (FK to the material type table)
PriceDT
Price

In the Material table, you should define the UOM for clarity. There is no need to ever change the purchase price. In your catalog table, you would enter the SalePrice. The catalog needs to be updated periodically also if you want to keep up with a volatile market price. The catalog table might need a child table that lists the components including their MaterialID and weight so you can change the price of each component separately.

 

[Allyj]

Thank you... No I don't think I did fully understand but I think I am starting to get it now.

What does PriceDT in your example refer to?

I think I would prefer to keep my materials in seperate tables but if you think this adds another layer of complexity, I'm willing to merge them.
I've got a weekend if kitchen demolition ahead, but after that I'll sit down and see if I can suss it out properly.
Thanks a lot!
Ally

 

[mike60smart]

Hi Ally
That would be PriceDate

 

[Pat Hartman]

If your app is already built with separate tables, I won't make you change it However, everything becomes more complex with your current set up. You also can't easily reprice stones or settings. So, if you have a gold setting in inventory and the price of gold goes up, you have no way of rationally updating the expected sale price except manually. By using a single table and keeping the material type (not just metal) and weight, You can modify the prices in the price table and then run an update query to change every unsold item in inventory. By keeping three sets of data, at a minimum, you have three price lists and three different update processes.

PS - consistency is your friend. Don't arbitrarily change the names of the same field like ValidDate, DateValid, ValidOn. The same field, the date on which the item was priced is named with a different name in each table. This just adds confusion. Once you standardize the names, you will recognize, that by adding a MaterialID field, you could easily use a single table to hold all the types of raw materials. You may want additional descriptive fields for stones. Don't mush the attributes into one field. Store them separately. It would be ever so much easier to find the VS2 diamonds.

PPS - do not use embedded spaces or special characters in object names. It just makes coding awkward. Access isn't Excel. It is no problem to use user friendly computer names and user friendly human names for each purpose.

 

[Allyj]

great advice Pat, consistency is a weakness of mine, it comes with being a creative person I've noticed!
But I fully understand your point.
thank you
I like the structure you have explained, I am going to have a go at doing it this way.
cheers Allison

 

[Allyj]

Hi Ally
That would be PriceDate

Thanks Mike, appreciate your offer of help I am going to address one issue at a time

 

[Pat Hartman]

You can get away with novice design choices when you are building for yourself and one other person who knows the business as well as you do. But, without a properly structured schema to rest on, all your forms/reports/batch data updating, etc will be a chore.

You are running a business and are not a professional developer so we'll try to help without being too pedantic but I think you're going to have to bite the bullet and do some restructuring if you want to minimize your task of keeping prices up to date. It's an ROI thing. If you can't easily update your cost basis, you won't do it and you'll be leaving money on the table.

I know you "never" have more than two types of metal in a piece or "one" type of stone but as soon as you exceed that, your structure falls apart but if you build it correctly from the outset, it doesn't matter how many types of metal or how many types of stones a piece contains, rather than having to have exceptions, it is really better, take my word for it because I have over 50 years worth of bad design choices to haunt me. Why do you thing I can answer so many questions? I've made pretty much every stupid, rookie mistake in the book. If you don't have time to do it right, what makes you think you have time to do it twice as an early manager used to say.

For me, when I realize I've made a mistake, it is easier to just delete the mistake and start again. For you, it kinda' depends where you are in your process. It is easy enough to melt down the metal and start the build again but if you're 99% done with the pave work, restarting is seriously painful. Software development is like that. Fixing mistakes early on can be slightly painful but late in the game, they will put you in the emergency room the pain will be so bad.

 

[Allyj]

Yes i totally agree and I actually enjoy the challenge. It's not too bad as I'm heading into winter and a quieter time to fix it. Appreciate your advice pat. If you don't mind when I think I've got the structure right can I send you the relationship diagram to look at?
Cheers Ally

 

[Pat Hartman]

Please post it here. That way every one can offer an opinion. We all love to offer opinions If I don't respond in a day or two, PM me, I might miss the post if I'm busy.