Register
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16
Off-Topic Lounge Thread, Database schema? in Numismatic Community; I am considering developing a web based interface to keep track of my collection (instead of my current spreadsheet system) ...
  1. #1
    Numismatic mingot's Avatar
    Join Date
    Oct 2010
    Posts
    143

    Database schema?

    I am considering developing a web based interface to keep track of my collection (instead of my current spreadsheet system) and I can probably take the easy way out and do away with backing the software with a real database of coins ... but what would be the fun in that?

    So I am thinking of having a general purpose online coin database.

    Anyone here even given any thought to that, or already have a schema?

    Or would anyone like to bounce some ideas around?

  2. #2
    Administrator Jesh's Avatar
    Join Date
    Nov 2009
    Location
    United States
    Posts
    2,700
    Quote Originally Posted by mingot View Post
    I am considering developing a web based interface to keep track of my collection (instead of my current spreadsheet system) and I can probably take the easy way out and do away with backing the software with a real database of coins ... but what would be the fun in that?

    So I am thinking of having a general purpose online coin database.

    Anyone here even given any thought to that, or already have a schema?

    Or would anyone like to bounce some ideas around?
    I'd do basically what PCGS, NGC, and Heritage has for their databases. I don't see any other fields that would be needed.

    If you upload an image to our gallery you'll see additional fields I added such as Reverse Details, Obverse Details, Catalog #, Mint/Mintmark, alignment and so on...

    What are you going to write it in?

  3. #3
    Numismatic mingot's Avatar
    Join Date
    Oct 2010
    Posts
    143
    The field are not so much of an issue, just trying to work out a good set of relationships. I'm a developer, so normalization is important to me lol. I'll post back when I get my thoughts together on a set of tables and relationships. "Coin" is going to end up as my table, with each entry representing a single ... well, coin. "Example" is going to be an instance of a coin, owned by a person, which will have information related to grade, price paid, etc etc. Now it's just figuring out which attributes of a coin to break out into tables and to normalize. Country, denom, type, mint, year, pcgs #, ngc# come to mind.

    I'll probably muck around with MVC.NET, get irritated, and switch to ASP.NET.

    I'm going to do the database in SQL Server (tons of instances here to play with), but will publish schema and data in case anyone wants to put it into mysql. I never do anything fancy or SQL Server specific, so it will be really be fine in any database.

  4. #4
    Administrator Jesh's Avatar
    Join Date
    Nov 2009
    Location
    United States
    Posts
    2,700
    Quote Originally Posted by mingot View Post
    The field are not so much of an issue, just trying to work out a good set of relationships. I'm a developer, so normalization is important to me lol. I'll post back when I get my thoughts together on a set of tables and relationships. "Coin" is going to end up as my table, with each entry representing a single ... well, coin. "Example" is going to be an instance of a coin, owned by a person, which will have information related to grade, price paid, etc etc. Now it's just figuring out which attributes of a coin to break out into tables and to normalize. Country, denom, type, mint, year, pcgs #, ngc# come to mind.

    I'll probably muck around with MVC.NET, get irritated, and switch to ASP.NET.

    I'm going to do the database in SQL Server (tons of instances here to play with), but will publish schema and data in case anyone wants to put it into mysql. I never do anything fancy or SQL Server specific, so it will be really be fine in any database.
    I'd do PHP and mySQL? ASP.NET blah!!!

    Could always integrate it into our forum. I was going to write something like that but I just don't have the time these days.

  5. #5
    Numismatic mingot's Avatar
    Join Date
    Oct 2010
    Posts
    143
    Haha, asp.net has been very kind to me.

    You'd be more than welcome to the schema/data/code so if you ever did find the time at least a good chunk of the thinking work would be out of the way. The PHP port would have to be an "excercise left to the reader".

  6. #6
    Administrator Jesh's Avatar
    Join Date
    Nov 2009
    Location
    United States
    Posts
    2,700
    Thanks I'll keep that in mind

  7. #7
    Numismatic mingot's Avatar
    Join Date
    Oct 2010
    Posts
    143
    So, I have gotten the schema from the top (country) all the way down to the bottom (coin).

    The issue I am having now is how to deal with "coin". PCGS seems to have separate entries for, lets say 1938-D Jefferson 5c and 1938-D Jefferson 5c FS. Ditto for mercs, Franklins, SLQs, and Roosies. I really don't want to "double enter" coins like that. I'd like things like that to simply be an attribute of a coin. This is cleaner, but present a few problems:

    1. I don't have a good way to have a one to one relationship between a coin and what pcgs calls a coin when it comes to the PCGS number. I'd like the benifits (for ease in linking) of the 1:1 relationship, but I don't like the fact that a query that just lists all the date/MM combinations now has to cull out things like full step "coins" to avoid have the list have double of everything. This does not even take NGC into account yet. I am not sure if they do different numbers for FS, 5FS, and 6FS. If so, double ugh.

    2. If I don't do a different coin for each then I have to have a good way for a different issues to have normalized strike characteristics. IE. the jefferson page needs to be smart enough to have a dropdown for FS/5FS/6FS where this field wont show on the penny table. It's doable, but it's going to be extra work to make this all database driven so that I can have generic pages regardless of the denom/series.

    Fun stuff. And this does not even start to get into the stuff where people are tracking die pairings. Which will probably present a pretty similar problem and need another table that relates things like valid VAMs to particular coins and lets the user select where applicable.

  8. #8
    Numismatic mingot's Avatar
    Join Date
    Oct 2010
    Posts
    143
    Ok, talking to myself here

    I think I know how I want to handle this one. It's going to be painful from a data entry perspective- but here goes:

    SubCoin table. Subcoin is going to look something like this:

    SubCoinId
    CoinId
    SubCoinName
    FSNumber
    PCGSNumber
    FSNumber
    ??Number (more of these. most nullable)

    So lets say I have a coin defined as a "1938-D Jefferson 5c" (ID:100) I would have a batch of related SubCoin entries that looked like this:

    1 100, "", null, "4001", null -- this part sorta sucks because everything is going to need to have at least one default subcoin where I can stash the "baseline" coin numbers.
    2, 100, "5 Full Steps", null, "84001", null
    3, 100, "6 Full Steps", null, "84001", null

    Notice that 5 and 6 are different "SubCoins" with the same PCGS number, since they don't make the distinction. I can probably extend this to track varieties when I don't feel like a variety should be it's own "Coin". It gives me the linking back to the individual TPC numbers and conveys other information about variety, color (for cents), etc etc.

    Overkill?

    My other viable option seems to just be to flatten this and to have a coin for each of what would be subtypes and to flag one of them as the "baseline" coin that I would display were someone simply displaying a clean list of (lets say) all of the date / mm combinations. That just seems so dirty for some reason.

  9. #9
    Administrator Jesh's Avatar
    Join Date
    Nov 2009
    Location
    United States
    Posts
    2,700
    PCGS has a coin list, it's big though.

    Just need Date, Grade, Mintmark, Denomination, Variety

    e.g. 1874, PR64CA, none, 10c, arrows

    if you want you'd need PCGS / NGC number but you'll need the book for that, and it's big (did i mention that)

  10. #10
    Numismatic mingot's Avatar
    Join Date
    Oct 2010
    Posts
    143
    I can probably parse the pages on the PGCS site to get all of those numbers without too much trouble. Keep in mind I am just talking about the internal number that PCGS assigns to a coin and not individual cert numbers for each graded example. I have no intention of trying to pre-load / keep up with any of that information other than cert numbers that an individual might attach to an example that they own.

    Mintmarks are also giving me some trouble with regards to how to represent them. For instance, "P". I know coinage with no mintmark is usually made in philly, but there are some exceptions -- pennies after a certain date might have been made at west point. '65-'67 coinage with no mintmark could have been made anywhere. Except SMS peices, which have no mint mark, but were produced in Philly. I can take the easy way out and just allow people to select valid mint marks for a particular country OR I can divorce mint/mint mark and store both for the cases where it's different and/or we really don't know. It's good information to know the place (and to have it stored in the database and displayed to save the user having to research) where something was made, even if there is no mintmark.

    "Grade" will come later when I start

 

 
Page 1 of 2 1 2 LastLast

Remove Ads

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •