Friday, September 21, 2012

Thinking about stamps and data models

I am in no way formally trained in relational databases, data modelling etc. but I am a couple of pages ahead of some. Having said that I think all that does is give me better opportunities to shoot myself in the foot, more quickly and with better accuracy.

So, that disclaimer out of the way, I've been thinking about how to represent a stamp catalogue and collections in a SQL database. I've been playing with MySQL Workbench as a simple design tool and while it's cute it takes quite a lot of work. A sheet or paper and a pen is a bit simpler at this stage.

So, what have I got so far? Not a lot to be honest. Below are some potentially incoherent ramblings and comments are welcomed.

There are the actual stamps that we possess - the physical bits of paper, and there is the issue - the details that are common to all the copies of the same stamp. Then in turn those stamps are likely to have been issued by and issuer as part of a series made up of a range of similar designs and a variety of values.

Breaking down the highlighted terms above:
  • stamps - these are the real bits of paper, each with it's own properties
  • issue - this is a description of the features common to the "same" stamp
  • issued/issuer - stamps are issued somewhere and somewhen by an recognised authority. This may be a national post office or a similar organisation, normally recognised by the UPU
  • series - issues are usually grouped into a series, all issued on the same date
  • designs - each issue will be based on a design which is sometimes used for multiple issues or a whole series
  • values - each issue in a series will typically be of a specific value and a series may simply be the same design consisting of multiple values (of a single currency)

In addition to the above there are common properties for stamps that can be applied to an issue:
  • size
  • shape
  • colour(s)
  • watermark (and perhaps paper type)
  • perforations
  • overprints
  • perfins

In turn, individual stamps may have their own properties which are either additional to the above or sometimes, in rare cases replacing the above:
  • condition
    • used / unused / cancelled-to-order
    • quality: mint, fine, good, poor etc.
    • quantity: single stamp or part of a partial or complete sheet (plus shape of sheet)
  • postmark
  • location - where have you put your stamp? shelf, album, page etc.

There are also more obscure properties that fall between an issue and stamps from that issue:
  • misprint

Once I started to look at the list of features and separate them out between the catalogue and the collection  - the distinction being issues go in a catalogue and stamps from that catalogue go in a collection - it became a little clearer but there is still a great deal of overlap. There are all these characteristics/properties/details - not sure what's the best term yet so I am going to be a technologist and stick to properties.

These properties - the ones above and those that I haven't yet included - can be grouped into categories such as physical, historical, commercial and so on.

Enough for now, I'm off to read more about Ruby on Rails.

Comments and addition, please!

    3 comments:

    1. Being "old school" sw-developer, I do have quite strong views on data models and database design. And to be quite honest, I've built quite many large scale databases using traditional methods. Even my personal stamp inventory database(mysql - about 650k rows) is pretty traditional).

      Anyway, if I were to start anew and make an public catalogue/inventory, I would likely do something very similar to what Reddit and several other social media sites have done. Here's a link: http://highscalability.com/blog/2010/5/17/7-lessons-learned-while-building-reddit-to-270-million-page.html

      I'm quite sure You might have read it on some point, but it never hurts to re-read good stuff again.

      Till next time, have a nice weekend,

      -keijo-

      ReplyDelete
    2. This comment has been removed by a blog administrator.

      ReplyDelete
    3. No irrelevant links please. Previous comment deleted.

      ReplyDelete