A Data Model for Collectors

No matter what you collect, you need to be able to organize (and trade, and swap) it! We look at a data model for collectors.

Collecting things is a very cool hobby. Some people do it even without knowing it: They start with one or two comics or vinyl records and somehow end up with shelves full.

At some point, all collectors want to know more about the items in their collection. Maybe they want to know what items are related and in what way. Maybe they want to know the value of an item or of their whole collection. Or maybe they want to swap items and need to keep track of their activities.

Just imagine what philatelists (stamp collectors) and numismatists (coin collectors) go through. These collections are usually measured in thousands (or tens of thousands) of items. Some have hundreds of thousands of items. Talk about needing a database to stay organized!

Are you a collector? Then you’ll enjoy this article. We’ll talk about a data model to help collectors manage their collections, find additional info, and more. Settle back in your chair and enjoy!

What Should Be Included in the Collectors Data Model?

For those of you who’re not so much into collecting things, I’ll answer a few questions before we start with the data model:

1. What is the main purpose of this model?

   The main idea is to build a data model we could use to store data from a web application. Users could create an account, add records of their collection(s), and add records for each item. A single instance of the web app would be limited only to one type of collectible, e.g. postage stamps. If a collector/user would like to have two kinds of collectibles, they would run two instances of the application.

2. What are the basic functionalities?

   The most important function is the ability to add items to collections. You (the collector/user) would access a set of predefined items and find your item. The app would give you additional information about your item and a list of related items. The main function here is a dictionary with a list of all possible collectibles.

   There is the way to populate that dictionary and compare your item automatically with values stored in the dictionary, but this is beyond the scope of our article.

3. What are some expected advanced features?

   Most serious collectors will already know the approximate value of their collections, or at least the value of their best pieces. Still, having the ability to (re)calculate the value of your collections using current market prices sounds pretty cool. So we should have that.

   Another cool option would be the ability to trade items with other collectors. You could do this to grow your collection or to earn some money. Collecting can be a great way to earn a living, if you know how to do it properly.

4. Is this model limited to collections of stamps, coins, and paper money?

   No, not at all. You could use this model for any collection you have. That’s why lepidopterology (collecting moths and butterflies) is in the title of this article. It’s not as popular as philately or numismatics, but there are people who love it. We call them lepidopterists.

The Data Model

The data model consists of four subject areas:

• Items & properties
• Statuses
• Collections
• Swaps & trades

Although the Collections subject area is the most important, I’ll start with the Items & properties and Statuses subject areas. The tables in these areas are prerequisites for the Collections subject area, which is a prerequisite for the Swaps & trades subject area.

Please notice that tables in the Swaps & trades subject area are related to the rest of the model. The collector and piece tables used in this subject area are copies from the Collections subject area. They are used to avoid overlapping relation lines.

Section 1: Items and Properties

First, I want to describe how to store items we could expect to have in a collection. All tables needed to do that are in the Items & properties subject area.

The most important table in this section is the item table. This is where we’ll store details related to any and all items that could be a part of a collection. (For philately, this is where we’d have records for all stamps ever issued. This in itself sounds like a big problem, but there are ways to build such a list.) For each item, we’ll store:

• item_name – The name used to designate that item. For the first postal stamp ever issued, this attribute would contain the value “Penny Black”.
• item_year – The year when this item was issued for the first time. This attribute could contain NULL when we don’t know the year when the item was issued.
• item_date – The date when this item was issued for the first time. This attribute will contain a NULL value when we’re not sure about the exact date. That will, of course, be much more often than for the previous attribute. If we don’t know the year, we definitely don’t know the exact date.
• country_id – A reference to the country dictionary, denoting the country that issued that item.
• series_id – A reference to the series the item belongs to. Series refers to a set of items that were issued together with different (or sometimes even the same) denominations. This value is not mandatory because some items are not part of a series (e.g. when a single postage stamp is issued) and some collectibles don’t have series.
• series_no – The ordinal number of that item its series, if any.
• denomination – The denomination, if any, used on that item. This value is in text format because different formats could be used for the denomination, e.g. “half-penny”, “20+3”, etc.
• image_link – A link to an image of that item in mint condition.
• description – An additional description, in free textual format.

We have already mentioned two dictionaries used to describe items, the country dictionary and the series dictionary. Both of them contain a primary key and a name attribute that we’ll use to uniquely denote the country or the series.

Now let’s consider all properties used to closely describe items. We’ll need three tables for this.

The category table contains a list of the categories used to differentiate properties. For postage stamps, we could use “size”, “color” and “perforation” as categories. Each category is UNIQUELY defined by its “name”.

Each category will have one or more properties. For the “perforation” category, properties could include “imperforated”, “perf 14”, etc. A list of all such properties is stored in the property table. For each record, we’ll store the UNIQUE combination of name – category_id and an additional description, if needed.

We will need to assign properties to each item. We’ll use the has_property table for this. It stores the UNIQUE pair of IDs referencing the item and the property tables. The details attribute could be used to store any additional descriptive information.

The last table in this subject area is the related_item table. We have already described one type of relationship, the one between items and series. But what if some items are not part of the same series but are still closely related? For example, the “Penny Red” stamp was issued to replace the “Penny Black”. It would be great if we could store these relations, and we do in this table. We’ll store the UNIQUE item_id_1 – item_id_2 pair and a textual description that describes the relationship.

Section 2: Statuses

The Statuses subject area is composed of just two tables, which are used to assign statuses to items in collections.

The status catalog contains a list of all possible UNIQUE statuses (i.e. conditions) an item could be in. For postage stamps, some statuses would be: “mint condition”, “unused”, “used” and maybe even “damaged”.

An item’s value is generally determined by its condition. In most cases, a postage stamp in mint condition is much more valuable than one that’s been used. We’ll store the “value” for each UNIQUE item_id – status_id pair in the status_value table.

Section 3: Collections

Now, we are ready to talk about collectors and their collections. We’ll use the three new tables in the Collectors subject area to store this important information.

All registered app users have a record in the collector table. For each collector, we’ll store:

• user_name – A UNIQUE value used to access the application.
• password – A hash value of the password.
• email – An email address used to register with the app.
• first_name and last_name – The collector’s first and last name.
• country_id – A reference to the country dictionary, denoting the country where the collector is located.
• address – The collector’s address.
• details – All other details, if any, related to the collector.

Each collector could have zero, one, or many collections. In most cases, each collector will have only one collection. Still, maybe some collectors want to keep their collections separate, or maybe they have one “for sale” collection while their other collection is theirs to keep. No matter how many collections a collector has, they are stored in the collection table. For each collection, we’ll store:

• collection_name – The name collector has chosen for their collection.
• collector_id – A reference to the collector. Together with collection_name, it forms the alternate key for this table.
• size – A calculated value based on the number of pieces (records) in that collection.
• description – Additional information, if any, about the collection.
• current_value – A calculated value denoting the value of the whole collection. This is based on the current available value of the items in the collection.

All collections are composed of items. Since we have already used the item table to define all possible collectible items, I’ll use piece for the name of the table that holds all collection items. So, piece is another name for an item in a collection. We could have multiples of the same items in our collection, e.g. we could have two “Penny Red” stamps. We’ll store each one as a separate record in the table because each could have a different status.

For each piece in our collection, we’ll store:

• collection_id – A reference to the collection the piece belongs to.
• item_id – A reference to one item. Please notice that the pair, collection_id – item_id is NOT UNIQUE because we could have multiples of the same item in a collection.
• description – All info related to that piece, in unstructured format.
• status_value_id – A reference to the status_value dictionary that denotes the current status and current value of a piece.
• status_from and status_to – The interval when an item had that status. We can expect that “status_to” will only be set if the stamp is lost, damaged, etc.
• image_link – A link to a picture of that piece. Storing an image is extremely important, especially when we want to trade pieces with others.

Section 4: Swaps and Trades

The last subject area in our model is the Swaps & trades subject area. I think it’s pretty obvious what will be stored here. Having duplicate items is not a rare situation at all. If you have thousands of postage stamps, you’ll probably have hundreds of duplicate stamps. So it would be great if we could swap them or sell them. To make that possible, we’ll use four more tables in our model. The remaining two, the collector table and the piece table, are just copies from other areas.

We have two tables between the collector and the piece tables. The reason for that lies in the fact that when a trade or swap takes place between two collectors, it could include one or more items.

This brings us to the explanation why all four tables are related to the collector table twice. In each trade or swap, we have to reference two different collectors. And yes, it does look cool in the model. ☺

You’ll see there are two branches in this area, one for trades and one for swaps. I’ll explain the trade branch first.

In the trade table, we’ll store:

• trade_time – The timestamp when that trade was confirmed.
• trader_1 and trader_2 – Are both references to the collector table and denote the two collectors involved in this trade.
• trader_1_conditions and trader_2_conditions – Are conditions both collectors agreed to for this trade.
• trader_1_payment and trader_2_payment – Are amounts collectors should pay to each other to make this trade possible. We can expect that one amount will be 0, while the other will be the amount the acquiring collector should pay to the other.
• trade_details – All additional details related to that trade, in free textual format.

Items that were part of any trade are stored in the in_trade table. Some trades could have only one item, while others could have several items going in both directions. For each record in this table, we’ll store:

• The UNIQUE pair of trade_id – piece_id. Notice that a piece can appear in a trade only once.
• collector_from and collector_to – Are references to the collector table, denoting the seller and buyer in the trade.
• item_value – The value of the item in that trade. It could be different from the market value if both collectors agree to it.

The swap branch structure is almost identical. Swaps are the same as trades, only without any payment. Two collectors could agree they’ll swap stamps just to get new items for their collections.

The swap table has the same purpose as the trade table. We’ll store each swap event in this table. For each such case, we’ll store the IDs of two collectors involved (swap_1 and swap_2), their conditions (swap_1_conditions and swap_2_conditions), and all additional swap_details.

The last table in our model is the in_swap table. It has the same structure and role as the in_trade table. I’ve kept it separate because I wanted to keep all swap and trade details separate. The item_value attribute is in the in_swap table to store the market value of that item when the swap took place.

What Do You Think?

In this model, I’ve tried to cover everything needed to build a web application that organizes collections, calculates market values, and allows trades and swaps.

But there is a lot we could add. I didn’t want to add negotiations, messaging, shipping, and complex payments in this model. I think that they are needed, but adding them would make everything much more complex (and this article much longer).

Would you like to expand the collectors data model? Would you add something or change something? Please tell us in the comments.