Suggestion on reducing overall database size.

[Fred Slota]

We now have I.[ItemDescription] separate from I.[Notes], where I.[Notes] is meant to have the base description of the issue, while I.[ItemDescription] is meant to have the variant-to-variant description.  I.Notes should be identical across #5, #5/A, #5-2, etc.  With the existing database structure, that's ideally a lot of repetition, since every variant should have the same I.[Notes], and realistically a lot of inconsistency, since frequently they don't.

 

Suggestion - the current I.Notes field should be moved into its own separate indexed N table.  All variants would have identical I.[NotesIndex] values pointing to the same N.[Notes] entry.  

 

Replace 1,000,000 I.[Notes] fields with 1,000,000 I.[NotesIndex] and what, 250,000 N.[Notes] fields?  That's gotta be a significant reduction in database size...

 

[Fred Slota]

Couldn't see the database Forest for the Notes trees...  Notes is the big offender/space-saver, but is not the only repetitive field across variants...

 

Database should be arranged around three tables (Title, Issue and Variant) and not the existing 2 (Title and Issue). 

 

Issue would be unique by TitleID, Issue and Type.  Issue would include fields like Notes, Storyline, Appearances, Writer, Artist, etc.

Variant would be semi-unique by IssueID, Variation (including regular) and Printing.  Variant would include fields like Item Description, Cover Artist, Quantity, Condition, Price, etc.

 

The interface could be made to mostly look the same, without letting on at what's happening under the hood.  Possibly want to rearrange the Edit Issue window to group Issue fields from Variant fields.  Possibly a little tricky to update the grid when an Issue field is modified, in that the change in a single box of an Issue field would need to be reflected in all variants of that issue.  Or, the interface could be modified to more naturally reflect the three levels somehow, maybe with Issue grid on the left and Variant grid on the right.

 

Really big reduction in size of the database, Not sure what the performance hits for a three-level data structure vs. a two-level data structure would be.