Anyone Using a Custom Database to Track Collection?

[NYDevilsFan]

I've always been a devotee of the inventory manager here on SCF, but I have also always kept a spreadsheet of my PC as there are a few additional pieces of information that I want to keep track of.

Last summer I took a database class and since then, being the computer nerd that I am, I have been considering creating an SQL database of my collection.

Part of my motivation would be to also use this as the base for a website for my collection. Setting up database queries for the various web pages I want to display.

Anyone else do anything similar to this?

[gfel2795]

I'm an Excel guy...I copy paste from Beckett and format everything for custom sorting. It is very time consuming and difficult to keep up with but it helps with trading/selling.

[scottkoz20]

Sent from my iPhone using Sports Card Forum mobile app

[NYDevilsFan]

Here is my initial design. Items I want would go into the GameWornJerseys and TradingCards tables. And items I actually own would go into the *InCollection tables. Game worn jerseys could only have 1 team and player, while cards could have multiple teams and players. Any feedback would be appreciated.

CollectionDatabase.png

[30ranfordfan]

I have never built out any DB for storing trading card info myself.... *IF* I were to ever build out a webpage of my own to show off my cards - I would probably want to do it then.

What I would suggest is this: WAY too much information is stored in the Trading Card table.

Add a lookup table for Sport (i.e. 1, Hockey.... 2, Baseball.... etc).
Add a lookup table for Manufacturer (UD, Leaf, Panini, ITG, Topps, etc)
Add a lookup table for Releases (or Brand) (i.e. "SP Authentic"). A release should be related to a Sport & a Manufacturer. Would contain the year, and release date too.
Add a lookup for a Set (i.e. SOTT, Shining Stars, Limited Logos, "Base"). This is the record I would then have a lookup field on Trading Card for.

*** You've got Manufacturer, Brand, Set, Subset all listed as columns. I might be missing something - but I feel like that's one too many. "Upper Deck", "SP Authentic" "Sign of the Times". (Unless you're going to split Rookies & Vets??). I'm just not sure the use for subset... though adding another layer here would be okay.

This would then de-clutter the actual TradingCard record, but would leave all the info easy to look up.

Next thing I would do: I would remove all of your boolean columns, and store them elsewhere.

I would make one table for Attributes. This table would have two columns: A PK, and a description (Rookie Card, Autograph, Memoribilia, Serial Numbered, etc, etc).

I would make another table called something like TradingCardsAttributesXref. This would have the ID of a Trading Card, the ID of an Attribute, and an optional text field - maybe you want to (sometimes? depending on the attribute?) add some kind of comment ("hard signed" for autographs, as an example).

The advantage of doing it this way - down the road if you think of something new you want to keep track of.... you simply add a new attribute, and start collection the data. The comment field will allow you to include notes about the attributes (Is it a rookie card? That' a simple yes /no.... but what kind of autograph? Is the GU piece a patch?).

When you display the information somewhere else, you'd need to look up all attribute records related to a particular card.


This would mean that the actual TradingCard table only has it's ID, and a FK column going out to set.



I would use lookup tables for anything that's possible. I would rather store "1" than "Red" in the Game Used Jersey table (for example) with the use of the lookup table that stores all possible colours. Again though, you can play with this in how you relate records. If GameWornJerseys is a table, and Colours is a lookup tables: You could make another XREF table that combines the two, as I described with the TradingCardsAttributesXref table. The real obvious advantage here... you'd have instant way of storing the details of multi-coloured jerseys. If the card is related to just "red" then it's one colour. If it's related to red, green, yellow, black - it's 4. Something like that.

[cheesecurd76]

I use Excel, like a dummy. There has to be a better way though. I'm hoping to upload a few hundred into my inventory on here and export it to my desktop somehow.

[scottkoz20]

I use Excel, like a dummy. There has to be a better way though. I'm hoping to upload a few hundred into my inventory on here and export it to my desktop somehow.

You can make a backup and then export the PDF file to you desktop

http://opg.sportscardforum.com/scf/inv/backup

[cheesecurd76]

You can make a backup and then export the PDF file to you desktop

http://opg.sportscardforum.com/scf/inv/backup

Greatly appreciate the info & the link Scott!

[scarrman]

I made an Excel files years ago. Includes Year, Brand, Description, Player Name, Card #, Serial/Odds #, Grade, Sport, Quantity, Value, Total Value. I have since added a multiplier as most of the collection is based on BV, so the multiplier recalculates the value - most of the cards have a X .1 multiplier so, for example, a common YG with a BV of $5 would have a collection value of $0.50 cents (helps for insurance valuation). This varies by player, and original value entered. I added a Team column as well. As I said, it has been years and since I have almost 14,000 items entered, I would never consider any other way of cataloguing. But searching is a piece of cake!

Oh, and I also have entered jersey colors, and if it needs a longer description I enter a comment box that includes a full description of a multi-colored jersey, patch, stick, puck, etc. Or multi-patch or player.

[Leafsman]

I use Excel. It's all I'll use. Example:

Spreadsheet.jpg