How should I handle types, varieties and errors in my coin DB?

March 16, 2010 9:09 PM

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!

posted by chndrcks to Computers & Internet (4 answers total)

Best answer: If every coin is of one variety, and each variety is of one type, you don't need to store the typeID but only the varietyID for each coin. Then you can model errors as an attribute of a coin's variety to which each coin record may also be linked. And since each variety and error may have been produced in multiple discontinuous ranges of dates, you should link each of them one-to-many to date ranges. So, something like:

types
typeID
name
designer
obverseDesigner
weight
diameter
edgeStyle

varieties
varietyID
name
typeID

coins
coinID
varietyID
datePurchased
purchasedFrom

errors
errorID
name
varietyID

coinHasError
coinID
errorID

varietyDateRanges
varietyID
startDate
endDate

errorDateRanges
errorID
startDate
endDate

posted by nicwolff at 12:22 AM on March 17, 2010

Oh, sorry, I missed denominations. That's just a parent table of types:

denominations
denominationID
denominationName

and then add the denominationID to the types table above.

(Also, I should have the name column in each table named after the table — typeName, varietyName, &c. — to make queries clearer and easier to write.)
posted by nicwolff at 12:28 AM on March 17, 2010

Response by poster: Thanks nicwolff, that helps a lot!

I have a couple questions on the error tables. Some errors happen to several varieties. The error "Double Die Obverse" could be applied to several varieties of pennies, nickels, dimes and quarters. Other errors, such as "S over horizontal S" happen to maybe only one variety. Would it be better to have these in their own table somehow and then link them to a variety? What I have in mind is something like applying the error "Double Die Obverse" to several different varieties.

Thanks again
posted by chndrcks at 7:09 AM on March 17, 2010

Best answer: If there's value in being able to see the same error across multiple varieties — e.g., "Show me all my coins that have double die errors" — then yes, you could link the errors table many-to-many with varieties by removing its varietyID column and adding a table like

varietyHadError
varietyID
errorID

so that when you link a coin to an error you're linking to the same error record everywhere. But it may not be worth the added complexity to the data model, since you could just search for "double die" in all the error names and get all those coins that way.
posted by nicwolff at 11:27 AM on March 17, 2010