One common discussion that comes up between shoe collectors is whether they keep their shoes in their original boxes, or if they toss their original boxes and keep their shoes in clear storage boxes. The clear storage boxes are nice and stay in tact, but potentially can change the color of the shoe over time due to light. Original boxes are always cool, but eventually they break down. Either way, you’re not going to mix- you either have all clear, or all originals. I opt for clear so that no matter what kind of shoe I have, the storage has a consistent look to it. The boxes are all the same size, and I can buy as many as I need for future shoe purchases.
In a way, the same goes for making an ERD for a database. You want the design to fit what you need now, but also be adaptable for future iterations. In this post I made a rough sketch of what my ERD might look like for my blog, but in this post I will go more in depth.
I have four sets of data that I will be looking at to analyze: Sales trends, number of releases, yearly sales, number of store openings/closures, and secondary sales. Below is a what the entire database may look like for my data sets.
As you can see above, the sales table would contain the brand, their sales in numerical amount as well as the year of that sales data. A brands sales are made up of many shoes, but a specific shoe can only be sold by one company (for example, the Air Max 97 is only sold by Nike). This brings me to the “Shoe” table, where the Shoe_SKU is the primary key of the table. Also in the table is the Shoe name, the number of times it has been released, the original price of the shoe and the brand (a secondary key of this table that connects as the primary key of the Sales table).
The sales table is also connected to the stores table with a one to many relationship, as one brand can have many stores, but many stores can only belong to one brand (Adidas and Nike do not share stores, and my data is only related to their stores and not secondary resellers such as Footlocker). The stores table will contain the location, the store status (open or closed), and the brand owning the store.
Finally, many shoes can appear on many resale sites, just as multiple resale sites will contain many shoes. The resale table will have Shoe_SKU as the primary key as it is in the Shoe table, as well as the original price, resale price, brand and size. Size being in this table will also give an idea of who is purchasing each brands’ shoes most frequently.