My items are listed as follows; This is just a summary of course. But I'm using a method shown for the "Detail" table to represent a type of 'inheritence', so to speak - since "Item" and "Downloadable" are going to be identical except that each will have a few additional fields relevant only to them.
My question is in this design pattern. This sort of thing appears many, many times in our projects - is there a more intelligent way to handle it? I basically need to normalize the tables as much as possible. I'm extremely new to databases and so this is all very confusing to me.
There are 5 items. Awards, Items, Purchases, Tokens, and Downloads. They are all very, very similar, except each has a few pieces of data relevant only to itself. I've tried to use a declaration field (like an enumerator 'Type' field) in conjunction with nullable columns, but I was told that is a bad approach. What I have done is take everything similar and place it in a single table, and then each type has its own table that references a column in the 'base' table.
The problem occurs with relationships, or junctions. Linking all of these back to a customer. Each type takes around 2 additional tables to properly junction all of the data together- and as such, my database is growing very, very large. Is there a smarter practice for this kind of behavior?
Item
ID | GUID
Name | varchar(64)
Product
ID | GUID
Name | varchar(64)
Store | GUID [ FK ]
Details | GUID [FK]
Downloadable
ID | GUID
Name | varchar(64)
Url | nvarchar(2048)
Details | GUID [FK]
Details
ID | GUID
Price | decimal
Description | text
Peripherals [ JUNCTION ]
ID | GUID
Detail | GUID [FK]
Store
ID | GUID
Addresses | GUID
Addresses
ID | GUID
Name | nvarchar(64)
State | int [FK]
ZipCode | int
Address | nvarchar(64)
State
ID | int
Name | varchar(32)
