An Antique Store Data Model

You might run an antique store because you love history, but you’ll need modern technology to keep everything organized! In this article, we discuss what an antique store’s database model would need.

Antiques are cool. I bet we all love history and antiques in some way. Some admire Victorian furniture, others are thrilled about old coins, vintage weapons, or medieval armor. Still others enjoy reading or just looking at old books or manuscripts. There are still many unsolved antique-related mysteries around the world, such as the Antikythera mechanism or the Voynich Manuscript.

So yeah, antiques are cool. If you get to run an antique store, you’ll probably see many unusual and valuable items. Maybe you’ll keep a couple for yourself, but you’ll sell most of the things that come your way. Either way, you’ll need to keep records! Creating a database model that would enable us to run an antique store is what we’ll talk about today.

Antique Store FAQs

Before we dive into the data model, we’ll first answer some common questions about the antiques business.

1. What is an antique?

   Although the word “antique” might remind you of the classical antiquity of Greco-Roman times, most antiques aren’t quite that old. While age is an important criterion for determining if something is antique, it only has to be 100 years old to qualify. Victorian furniture is definitely an antique item!

2. What about vintage items?

   Vintage items are also very popular. They are not as old as antiques, but they are still part of the past. Most vintage items are between 50 and 100 years old; some vintage things, like clothes, only have to be 20 years old.

3. What is the difference between an antiques store and a pawn shop?

   An antique store usually sells only vintage and antique items. A pawn shop can sell many kinds of valuable things. In some cases, an antique store can function like a pawn shop: it will loan money to a customer and take an item as security for the loan. If the customer does not redeem the item (by paying back the loan) by a certain date, the item becomes the property of the shop and can be offered for sale.

   Because of this overlap, I’ve designed this model to work for both classic antiques stores and pawn shops.

4. What kinds of transactions can we expect in an antique store or pawn shop?

   We can expect most of our available items will be ones we bought, those from our personal collection, or those that were pawned with us. Our personal items or those that we bought are immediately available for sale. Pawned items become available only after the customer fails to repay the loan.

   We can also expect that some customers will bring in items just to get an estimate of their value. After we make our valuation, the customer can decide to sell the item to our store or to keep it for themselves.

5. What should you know before going into the antiques business?

   Reselling sounds like easy money, so many people might decide to go into such a business. However, there is a lot of competition, which will significantly increase the chance of failure.

   Besides making a profit, we’ll have many other decisions to make. We need to price items in such a way that keeps the cash flow going, which might mean organizing special sales or sometimes giving discounts. We need a strong online presence, and we have to be skilled at targeting the right customers using online and offline methods.

   Also, an antiques expert should be able to recognize possible opportunities (such as the proverbial Van Gogh painting in a rummage sale), give an accurate estimate of items’ values, and uncover any interesting stories behind items (e.g. a sword that can be traced back to King Henry VII will be more valuable than another sword that comes from the same era). This is mostly non-technical work, but we’ll need to store the results in our database.

6. Will this model be limited only to antique and vintage items?

   No. You can use this model to buy and sell items of any age. In real life, it’s usually the antique and vintage items that have the most interesting stories attached and bring the most value, so many stores will work only or primarily with such items.

7. What should the app do?

   Usually, almost all the items available for sale will be on display in the store. Most of them will have a printed description and price near or on them. When a potential buyer asks for additional information about an item, we should be able to access it quickly by searching for the item id. On the other hand, when a customer wants to sell or pawn an item, or just ask for an estimate of its value, we should be able to enter everything related to that item and transaction on one screen. I would personally expect an application installed on a single machine, with one simple screen for each operation.

The Data Model

The data model consists of three subject areas:

• Items
• Item statuses
• Clients and invoices

Plus, there are two tables outside of any subject area:

• Employee
• Country

I’ll start with these two tables because they are referenced in other subject areas, and then I’ll describe each subject area separately.

The Employee and Country Tables

These two tables are not specific to this data model, but they are still needed.

Our store probably will have more than one employee. In that case, we need to keep employees’ details to be able to record which employee performed an action (i.e. made a sale, generated an invoice). We’ll use the employee table to do this. It will only store employees’ first and last names, but if needed we could add other attributes as well.

Our operations may not be limited to a single country: we could buy items and contact clients from other countries. The country dictionary will be used to categorize clients and items by country. We’ll use only one attribute, country_name, which holds only UNIQUE values.

Subject Area 1: Items

In the Items subject area, we’ll store details related to every item we’ve ever had in our shop. This part of the model is simple, but it’s sufficient for our data needs.

In this business, it’s all about the items. When a dealer sees a new item, they should be able to estimate its value according to its age, any hallmarks or signatures, general design, etc. This estimate will be used to determine the price the dealer is willing to pay for that item.

We may also buy items at auction rather than directly from customers. After an item is purchased, it’s usual to conduct more research so that the item can effectively be marketed to potential customers.

The most important table in this subject area is the item table. It contains a list of every item we currently have or have ever had in our store. The attributes in this table are:

• item_number – An UNIQUE internal identifier we’ll use for that item.
• item_type_id – References the item_type dictionary and states what the item is.
• item_description – A textual description which stores all the unstructured information we have for an item. We can expect to store different information for different item types; plus, the same data will not be available for every item.
• item_picture – Links to the location where the item picture (if any) is stored.
• date_produced – The date when the item was produced or created. It is a VARCHAR type because we will have exact dates for some items (e.g. a book that has the exact date when it was printed on the title page) while others will just have a year or some other approximate time (e.g. a Victorian sofa produced in 1848). This attribute can contain NULL values because we may not even know the century when some items were produced.
• country_id – The ID of the country where the item originated. We’ll use current sovereign states’ names rather than referring to countries that don’t currently exist. If an item was made in Vienna in 1848, then the “country_id” would reference today’s Austria and in the description we would write that the item was produced in the Austro-Hungarian Empire.
• available – A Boolean value stating if the item is currently available for sale. Available items are those we’ve bought from customers and haven’t yet sold. Pawned items also become available after a certain date. Until that date, the customer who pawned the item can buy it back. Items that are in our store just for valuation are obviously not available for sale. We’ll update this attribute each time the item changes its status in the status_history table.

The item_type dictionary is used to categorize our items by type. The only attribute in this table is type_name, and it can contain only UNIQUE values. Item types are defined by each store owner, but some expected types are “book”, “furniture”, “clothing, “weapon”, etc.

We could also define a set of attributes that could be used to describe each item of certain type using “key” – “value” pairs, but that would complicate the model. We can achieve almost the same functionality using the item_description attribute.

The author_role table is a dictionary that defines every role an author could have. For a book, roles could include “writer”, “lector”, and “editor”. The only attribute in this table is role_name and it contains only UNIQUE data.

The last table in this subject area relates items with authors and their roles. It contains only foreign keys from the three referenced tables, and the item_id – author_id pair is the UNIQUE key of this table. We expect that an author will be assigned only one role per item.

Subject Area 2: Item Statuses

The Item statuses subject area contains only two tables. They are used to define the current status of an item and to update an item’s availability.

A list of all possible statuses is stored in the item_status dictionary. Beside the primary key, the table has only one attribute, status_name. This attribute can contain only UNIQUE values. Some expected values for this dictionary are “item bought”, “item sold”, “item pledged”, “item evaluated” and “item available”.

We’ll assign a status to each item in our store. An item’s status may change over time. For example, if we evaluate an item for a customer and then buy it from them and put it in our store, its status changes from “item evaluated” to “item bought” to “item available”. All these statuses are stored in the status_history table. For each status, we’ll store foreign keys for the related item and its status in the item_id and item_status_id attributes.

When we update an item’s status, it’s important to define the dates when the previous status began and ended. When we buy or sell items, these two dates could be the same. When an item is pawned, the actual date the item was pledged is the start_date; the end_date will contain the date when the pledge expires and the unredeemed item can be offered for sale.

Subject Area 3: Clients and Invoices

Our final subject area is the Clients and invoices subject area. It consists of five tables that relate the other tables and subject areas in this model.

The most important table in this section is the invoice table. It stores all invoices issued to customers and all payments we’ve made. The attributes in this table are:

• invoice_type_id – References the invoice_type dictionary.
• issued_by – A text description of who issued the invoice. If we sell or evaluate an item, our details will go here. If we buy an item, the customer’s details will be stored in this field.
• client_id – References the client table. We’ll store this value when the customer is also a client in our database.
• issued_to – A textual description of who received the invoice. It uses the opposite logic of the issued_by attribute.
• time_created – The actual timestamp when the invoice was issued.
• invoice_amount – The total amount due on that invoice.
• tax_percentage – The tax percentage. If we operate in more than one country, it’s likely that each tax percentage will be different. Also, tax percentages can change over time, so it’s important to store the applicable percentage for that invoice.
• tax_amount – The total tax amount for the invoice.
• currency_id – References the currency dictionary. If we buy or sell items in different countries, it’s best to store the currency that was used for the transaction.
• invoice_amount_currency and tax_amount_currency – The total invoice amount and the total tax amount, stored in the currency used for this transaction.
• employee_id – References the employee ID of the person who generated that invoice.

The invoice_item table relates invoices with the items that were part of that transaction. Beside the primary key, this table has only two foreign keys. That foreign key pair forms the UNIQUE key for the table.

A list of all our clients is stored in the client catalog. This table is simplified; it only has clients’ names and the countries where they live.

We’ll reference the currency dictionary when recording the original currency used to pay an invoice. The code attribute can contain only UNIQUE values. Probably we’ll use international currency codes like “EUR”, “USD”, “RUB”, “PLN”, “HRK”.

The last table in this model is the invoice_type table. Its values are used to denote the type of invoice, such as “buying”, “selling”, “pledge” and “valuation”. As with the other dictionaries, its type_name can contain only UNIQUE values.

The model we discussed today should cover every situation faced by the manager of an antique store or pawn shop. Some parts of the model could further be adjusted to suit specific needs. I’m mostly thinking of the dictionary tables: describing items and adding prices for each item. Still, this data model can serve as a backbone for developing a store application.

Please feel free to comment and share any ideas on how to improve this model.