Designing two tables based on the following scenarios

Other SQL Server Topics
1

I want to design two tables considering the following scenario. Consider the image below:

Screenshot 2024-04-08 200000
Screenshot 2024-04-08 2000001026×376 16.5 KB

I’m moving blue boxes (which shows up on screen after Show Options the button is clicked) from left to right (on the grid) after clicking Move Text Content! button. Here are the steps I follow:

1.Select one option from the Select options dropdown.
2.Select a color from the dropdown.
3.Click the Show Options button
4.Enter a row and column (For example A and 1) and then click calculate Hit the Move Text Content! button to move blue boxes on the grid.

So, once "Move Text Content!" button is clicked, I want to save the information in the database tables. Let's say if my actual select statement is the following for discussion purpose:

<select> <option value="101">Capricorn</option> <option value="102">Aquarius</option> <option value="103">Pisces</option> <option value="104">Aries</option> <option value="105">Taurus</option> <option value="106">Gemini</option> <option value="107">Cancer</option> <option value="108">Leo</option> <option value="109">Virgo</option> <option value="110">Libra</option> <option value="111">Scorpio</option> <option value="121">Sagittarius</option> </select>

Selecting Capricon produces 4 HTML elements like following:

<div data-id="43">
<span class="words" style="color:black;background-color:white" data-id="0">Capricon#1</span></div> 
<div data-id="44"><span class="words" style="color:black;background-color:white" data-id="0">Capricon#2</span>
</div>
 <div data-id="45">
<span class="words" style="color:black;background-color:white" data-id="0">Capricon#3</span></div> 
<div data-id="46"><span class="words" style="color:black;background-color:white" data-id="0">Capricon#4</span>
</div>

Is the following design okay?

I'm thinking about having an OptionsTable where id is a primary key and valueID will store the value of Capricorn

So table will contain an entry of id with value 1 and valueID value as 101

Id valueID
1 101

And then the SelctedOptions table would contain other details like this:

optionsID valueID selectedValueID Row numericalValue color
1 101 43 A 1 black
2 101 44 A 2 Black
3 101 45 A 3 Black
4 101 46 A 4 black

Here optionsID is the primary key of the above table.

2

In the SelectedOptions table, the key should be:
( valueID, optionsID )

And the color should be a code, with a separate Colors tables. For example, 1 = 'black'.

3

Could you elaborate on what kind of key (valueID,optionsID) you are suggesting?

And by having another table for color, I would need to join that table with SelectedOptions one whenever I want to figure out color I believe.

Thanks for your reply.

4

Since SelectedOptions is a "child" table, its first key should be the key of its parent table. That is standard data modeling.

Yes, you would have to join to another table to get colors. But you avoid issues with a possible color change later.

5

What type of key you are referring to when you say this? Foreign key with ( valueID, optionsID ) combined?

6

The primary key, as you referenced. Despite what people think, there is no rule that every primary key for every table has to be an identity column or equivalent.

7

So there doesn't need to be Primary and Foreign Key kind of relationship between the two tables?

The primary key with ( valueID, optionsID ) in child table can be separate from primary key id in the parent table?

8

You should also have a FK that points valudID back to the its original table. I don't see why you need an "id" and a "valueID". Again, not every table needs an identity column! That's just a dangerous myth that causes people to misdesign tables all the time.

1 Like
9

I'm assuming that once valueID is assigned it will never change. That is important for table design too.

10

Yes, those values 101, 102 etc will never change.

So my table could look like the following with valueID as primary key?

valueID
101

Do you think this table should contain any other thing as it's just going to be a parent table with one column and rest of the things going inside other child table.

Can the Foreign Key be valueID from SelectedOptions table which points to the primary key of the parent table?

11

Yes, valueID itself would be the key. Normally every table will have at least 2 columns. Often one column is just a name / description for the key value.

Yes, the FK would be from the child to the parent table based only on the parent key.

12

Thanks. So the tables could look like the following then:

image
image637×651 6 KB

Clarification on FK:

It will be a combination of valueID and selectedValueID pointing to child table (SelectedOptions) to the valueID of parent tableOptionsTable?

Secondly, I was wondering how would you handle the update feature on this design. I mean, there's not going to be move button involved in case of updating the text once it has landed on the grid as far as I can think of. I was just planning on having a button on top of grid such that once users are done editing the text on the grid, they can just hit that button and update it.

13

You could have an id (identity or sequence) column in the SelectedOptions table. It's not that identities are totally wrong, it's just that not every table needs them.

You would have to handle assigning numbers to colors and changing color names to numbers in the background. Correct, the user should see none of that.

14

Thanks. I realized that I might not need to have OptionsTable and just the SelectedOptions table since all I care about are the details on the grid. So this might make things simpler in terms of just having two tables - SelectedOptions and Color

15

Hey @ScottPletcher ,

Based on what I was thinking, I came up with this one table approach and wanted to get your inputs on this. In the original diagram I showed, there are couple of more items added, like Barcode and Plate Name. Barcode is going to be unique always unless user starts a completely new Move Testing and hence the Barcode column is going to be same for one set of move testing. Plate Name is going to change for each valueID. So basically, every time user clicks on Move Text Content button, the plate name will be different as shown below.

Table Name: SelectedOptions

id valueID selectedValueID avaliableItems numericalValue rowValue columnValue color Plate Name Barcode
1 105 43 Text1 1 A 1 1 181130KL-RA-17IGI F0155982
2 105 44 Text2 2 A 2 1 181130KL-RA-17IGI F0155982
3 105 45 Text3 3 A 3 1 181130KL-RA-17IGI F0155982
4 105 46 Text4 4 A 4 1 181130KL-RA-17IGI F0155982
5 106 54 Text5 1 B 1 2 182230KF-DS-18PXT F0155982
6 106 55 Text6 2 B 2 2 182230KF-DS-18PXT F0155982

Since Barcode column contents are repetitive, do you think this is a good design to keep everything in one table or should I start with a PArent table holding just the Barcode and then child table can contain the rest of the columns which are show in the above table except the Barcode column?

16

Proper normalization normally needs to more tables, not fewer. In general, don't try to force/cram everything into 1 table.

17

OK, makes sense. Could you take a look at this design where I have a separate Barcode table containing unique values and then the SelectedOptions table containing all the barcode table related details. Color tabe will be as before and I haven't included it below.

Table Name: Barcode

Id Barcode EnteredBy Date
1 F0155982 1198 04/25/2024
2 A0155983 1120 04/23/2023

Table Name: SelectedOptions

Id barcodeId valueID SelectedValueID availableItems numericalValue rowValue columnValue color PlateName
1 1 105 43 Text1 1 A 1 1 181130KL-RA-17IGI
2 1 105 44 Text2 2 A 2 1 181130KL-RA-17IGI
3 1 105 45 Text3 3 A 3 1 181130KL-RA-17IGI
4 1 105 46 Text4 4 A 4 1 181130KL-RA-17IGI
5 1 106 54 Text5 1 B 1 2 182230KF-DS-18PXT
6 1 107 55 Text6 2 B 2 2 182230KF-DS-18PXT

  1. Is it looking better?

  2. Do you think I should consider more tables since valueID and PlateName columns also contains duplicate values. If yes, could you share your thoughts about this?