Anyone Using a Custom Database to Track Collection?

[scottkoz20]

Greatly appreciate the info & the link Scott!

That should be csv, not pdf


Sent from my iPhone using Sports Card Forum mobile app

[sparty07]

I use Google Sheets so I always have access to my "file" and can easily share it.
https://docs.google.com/spreadsheets...it?usp=sharing

[NYDevilsFan]

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.

Thank you! This is exactly the feedback I was looking for. The other thing I think I want to change is to combine the PlayersOnCards and TeamsOnCards so I can tell which player is represented with which team. For example if I have card that has 6 different players with 6 different teams, I may only want to display the card if it has Player X pictured on Team Y which would be impossible to tell with the current layout. I am going to start working on making these updates now.

[30ranfordfan]

Yeah, exactly. If players ans teams are their own tables... And there is a table for cards... You can create a table where each row is card id, player id, team id. Something like that

Sent from my LG-H915 using Sports Card Forum mobile app

[NYDevilsFan]

Still some work to do, but this is what I got done tonight.

I wasn't totally sure how to handle print run and serial number.

Some sets have a different print run per card - on example would be The Cup Rookie Gold that are numbered to jersey number. So for this reason I put it on a per card basis rather than per set.
Obviously not every card has a known print run or serial card.

CollectionDatabase - update 1.png

[creasecollector]

Nice discussion, guys. Those tables are really intense. Love it.

I use the SCF Inventory for my PC stuff, but I've also used other resources or my own personal knowledge of the cards that are out there to create a more complete wantlist of my PC guy and post it up on my PC 'showcase' website (Weebly link located in signature).

It's simple, works well, and lets other collectors know what I'm looking for. Not as advanced as some of you folk. If I had more of a collection to keep track of, then I would definitely look to go that route, too.

So interesting to see what other collectors are up to when it comes to topic like how us collectors are hunting, storing, or keeping track of their cards, etc.