I am trying to figure out the best way to set up my tables for this data. I have some work queue data that users will be accessing and reporting from, I have a main table set up for about 15 pieces of common data that they all have, I have a comments table for the users to enter comments about the records, so a one to many relationship from main to comments. Then there are other pieces of data, some records have 1, some have a few, some have 10, could be numbers, money amounts, dates or strings. This is the data I am not sure how to handle properly.
One thought was to place all possible values as columns on the main table, but future data could introduce additional fields and it would just grow.
Another thought was to have a table of additional data with three columns, FK to link to the PK of the main table, and two strings, one to say what the data is, the other to be the value. But that doesn’t seem right either.