How should I handle types, varieties and errors in my coin DB?
March 16, 2010 9:09 PM Subscribe
How should I handle types, varieties and errors in my coin collecting database?
Before I get to the meat of the question, I'll give you the terminology I'm using (because I'm not sure all of it is standard)
The first grouping (as far as my mental sorting goes) is: denomination (1 cent, 2 cents, 3 cents, 5 cents ... $50)
Within denominations, there are types. For example, the 1 cent denomination has the following types: flying eagle, indian head, Lincoln wheat ears reverse, and Lincoln memorial reverse.
Within types, there are varieties. For example, the Lincoln wheat ears reverse has 2 varieties: Variety 1 - Bronze and Variety 2 - Steel.
Within varieties, there are errors. For example, some (but not all) 1917 Lincoln Wheat Ears (Variety 1) have a doubled-die obverse.
With that out of the way, let me give you my original, simpler approach to this DB. My first plan was to have a type table that would look something like this: typeID, Name, yearsProduced, compositionID, obverseDesigner, reverseDesigner, weight, diameter, edgeStyle. In addition to other information (e.g. datePurchased, price), each coin in the DB would be assigned a typeID.
I quickly realized that this wasn't going to work well for types and it was a real pain for errors. I ended up with Names like "Lincoln Wheat Ears (Bronze)" and "Lincoln Wheat Ears (Steel)" which seems like the wrong way to do it. When you start adding in errors, it gets unwieldy.
I thought about having a separate Varieties table, but here are the problems I'm having with it:
1) What separates varieties for one type may be completely different from what separates varieties of another type.
For example: the wheat ears I mentioned earlier had varieties that were distinguished by composition (bronze vs. steel). They also have different weights.
Other types have varieties such as "Arrows at Date" vs. "No Arrows at Date"
Some have varieties such as "Large Diameter" vs. "Reduced Diameter"
In short, almost every field I had in the Type table could be what distinguishes one Variety from another.
2) Dates. I wanted to keep track of what years each date or variety was produced - for three main reasons. First, when I do the front-end, I'd like to be able to enter a denomination and year and have it list all the eligible varieties/errors. Second, I'd like to be able to list all the types/varieties/errors that were produced in a given year. Third, when I look at a type/variety, I'd like to be able to see what years it was produced.
I'm not sure the best way to do these dates anyway, but what further complicates them is that there are often gaps. For example, the Lincoln Wheat Ears Variety 1 ran from 1909-1942 and 1944-1958, with a gap in 1943 when the Variety 2 was produced. This may not matter, but sometimes Varieties share a year (2 varieties of the same type could be produced in the same year)
3) A separate Varieties table doesn't help much with errors. I could treat errors as a variety, but it would start to get almost as unwieldy as my original Type table.
Hopefully this all makes sense.
My question then is how should I handle types/varieties/errors in my database?
It seems like a Varieties table is the way to go, but I don't know what fields it should have (see 1) above)
Thanks in advance DB design gurus!