I’ve been spending a lot of time designing the database for this application. I’m not a database guy in my day-job, so this is a little more complicated than I had originally planned. And the databases I usually deal with do not have these types of disparate inventories. I found that the user tables and those types of common tables were pretty easy to set up, but the core inventory tables are proving to be more work than I had anticipated.

Here’s my problem: I want to have a set of generic tables where the user’s inventory items get stored into. When I say generic, I mean that all the different types of inventory would be stored in the same tables. So a 1794 Liberty Cap Large Cent would be stored in the same table as a 1957 HO Scale Revell 0-6-0 Omaha Union Pacific Engine. Of course each part of those descriptions should not all be in the same field either. And some items (like coins) have a lot less descriptive fields than others. How do I know how many fields to have on the database table?

The reason I want to do this is because I will have different screens displaying collection reports, and I don’t want to have to create a whole slew of new, nearly identical custom report screens every time I add a new collection.

I’m experimenting with some theories I have for handling this problem. but hey, if you have any real world experience doing this, or clever ideas on how to best handle something like this, please let me know. I’d really appreciate your input.