Question about normalizing and hence using correct design

[Jack_Tauson_Sr]

Let’s say I have the following table without normalization:

Design 1: Without normalization:

Design 2 : Considering Normalization

Here are some of the tables that are possible based on the above table info:

valueIDs: ( valueID, name ) --key=valueID
valueIDValues: ( valueID, numericalValue ) --key=(valueID, numericalValue)
valuePartialStorage: ( valueID, rowValue, color, PlateName, Barcode ) --key=(valudID, rowValue?)
valueDetailedStorage: ( valueID, selectedValue, availableCases ) --key-(valueID, selectedValue)

where valueIDs is the parent table and all other tables are child tables.

So the table would look like the following with the data:

As seen above, Table 3 is still having some duplicate values. So I wanted to know if I’m heading in the right direction? Do I need to split Table 3 into more tables where 2nd normal form condition is satisfied?

[DarthGuido]

I would love to help you, but I honestly have no idea what those values mean, what the relationship between the columns is, and what the primary key of the main table is?

[m_hutley]

1. What you want to store info about;
2. What info you want to store about them;
3. What relationships exist between them.

[r937]

i too am having difficulty figuring out what’s going on with all your columns

but one thing caught my eye

Table 3 is still having some duplicate values

normalization is ~not~ about removing duplicate values

normalization is about the functional dependency of attributes to candidate keys

huge difference

[Jack_Tauson_Sr]

Hey @m_hutley ,

Since you are into Javascript as well, I thought I would explain it that way how things are happening. Maybe that might help others understand the whole scenario as well. So I have this JSFiddle and I’ve included the screenshot below to explain.

1. So a user select one option, color and click on Show options button

2. Enters a row letter (from A-H)

3. Enters a column value (from 1-12)

4. Click on Caculate button

5. Enters Barcode - this value always remain the same as long as I am working on this page.

6. Enters Plate name. This will keep changing with each selected option from the Select options dropdown. So if I have selected OptionOne there will be one Plate Name. If I select OptionTwo the plate name will be different

7. Click on Move Text Content button to move contents on the grid.

So I want to store all of these info when the Move Text Content button is clicked.

1.valueID is the value of the selected option from the dropdown…

2. valueName in my table is OptionOne shown in the JSFiddle and CaseOne shown in my table data.

3. numericalValue starts with the column value entered in Enter a column 1-12 text input and depends upon how many options shows up when Show Options button is clicked. In case of 105, I have considered let’s say it shows 4 options so there are 4 values for valueID 105.

So I think the above explanation answers some of your and other’s question? Let me know if I can explain more. Thanks!

[Jack_Tauson_Sr]

For Table 1, I was thinking of having valueID as the primary key

[m_hutley]

(Stream of conciousness thread as i look at your post.)
So… I look at this page, and my first impression is that your primary table is called “Testings”.
It feels like there would be an Options table, a Colors table, and a join table between them (Options_Colors). This table would likely be a foreign relation to the Testings table.
Why does the user choose an option, then click a button marked show options? They’ve already chosen an option.
Row and Column feel like fields in the Testings table.
I’m not sure where Cell Number comes from, but if its a calculated value, it may or may not be a field in the Testings table.
If Barcode never changes, why is it an input?
I dont understand Plate name at all. Are you calculating that value somewhere?

[Jack_Tauson_Sr]

@m_hutley

Why does the user choose an option, then click a button marked show options? They’ve already chosen an option.

In actual code, I have a No Selection option in addition to OptionOne and OptionTwo. So user will have to select an option. But for simplicity I didn’t include it in my code.

@m_hutley

I’m not sure where Cell Number comes from, but if its a calculated value, it may or may not be a field in the Testings table.

selectedValueID is the cell number which is a calculated value.

@m_hutley

If Barcode never changes, why is it an input?

Once user is done filling all 96 squares, and when they plan on start working on something new, barcode will change. But as long as the cels are not completely filled, barcode will remain the same.

@m_hutley

I dont understand Plate name at all. Are you calculating that value somewhere?

Nope. So whenever user selects an option and click on show option, there will be a plate name user will be putting it and it will again change, once user selects another option from the dropdown and text contents are moved to squares. So with every selection and move button click the plate name will keep on changing.