February 24, 2016 at 8:32 am
Hello! I am working on a data model for a new module in our system. I would like to get some opinions on how to to best store some data in the tables. The data is a series of fields called "blocks". The blocks are numbered from 1 to 42 and some blocks are 23a 23b 24c etc... Each "block" contains a value and are all different types of descriptor data. Users will be entering this data into sort of a form on the front end. My problem is that some blocks will have multiple data sets. IE: not a one to one relationship between the block and its value.
So for example, Block 34c may have 4 different values. It is also not practical or desirable to store the values all in one field. We will have to do reporting on this data and we need each data point to have its own field.
None of this data exists yet, its all logical at this point, so I don't really have any code or raw data to provide other than my descriptions.
So, I am trying to figure out what the best table structure would be to store the block values. I had come up with a TBL_BLOCK_VALUE table but this table only works if there is 1 value for each block. Would it make sense to have a TBL_BLOCK_LINE_VALUE table that could somehow link the blocks with multiple values? Or am I totally thinking about this the wrong way?
I apologize if I didn't provide a clear enough example as I know its hard to understand what I'm talking about without seeing it. Its also hard for me to come up with a design without seeing it too! 🙂
February 24, 2016 at 8:39 am
Or I was just thinking (could it be this simple?) ... if I just created a table that was like this:
Row_id Issue_num block_number block_value (as a sql_variant)
1 1 1 XXX
2 1 2 1356246
3 1 3 Some Lovely Text
4 1 3a L3235
5 1 3a P4232
EDIT: Nevermind, its not that simple. Cause if someone edits the values for 3a, I wouldn't know which 3a was being affected.
February 24, 2016 at 12:37 pm
Normalize by block number?
February 24, 2016 at 2:10 pm
Are the blocks specific to one issue or "shared" across issues? For now, I'll assume that Block is a completely independent entity. [If it's not, we'll need to adjust the table structures.]
--Format is: Tablename ( column_name1, column_name2, ... )
Issues ( issue_number, date_created, ... )
Key = issue_number
Blocks ( block_number, date_created, ... )
Key = block_number
Issues_Blocks ( issue_number, block_number )
Key = ( issue_number, block_number ) --NOT an identity column!
Blocks_Data ( block_number, identity_or_sequence#, data )
Key = ( block_number, identity/sequence# )
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply