Good and bad design

Other SQL Server Topics
1

I have two designs in my mind concerning the following tables. The DesignOne is a bad design in my opinion as it contains everything in one table and a lot of repetitive values in the column - valueID, valueName, rowValue, plateName and barcode as shown below:

DesignOne

Table Name: completeStorage

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

The DesignTwo is an improvement to above one where I've split things into two tables

DesignTwo:

Table Name: PartialStorage

valueID valueName rowValue columnStartValue color columnEndValue PlateName Barcode Date EnteredBy
105 CaseOne A 1 1 4 181130KL-RA-17IGI F0155982 04/11/2024 11
106 CaseTwo B 1 2 2 182230KF-DS-18PXT F0155982 04/11/2024 11

Table Name: DetailedStorage

valueID selectedValue availabeCases
105 43 Text1
105 44 Text2
105 45 Text3
105 46 Text4
106 54 Text5
107 55 Text6

So in the DesignTwo I have taken care of duplicate stuff at many columns but I'm still concerned about the valueID column. Is it still considered a bad design since I'm having repetitive valueID values in DetailedStorage table and barcode values repeating in PartialStorage table?Is yes, then any suggestion as how can I improve this?

2

For entity/table design, follow the normalization process. In particular, no repeating values and all columns in a table must relate directly to the key, the whole key and nothing but the key of the table.

Just quickly, with the very limited info available, I would say maybe start with:

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)

If/when you truly normalize, you will end up with (many) more tables than you initially expected.

1 Like
3

Could you name the type of keys you are suggesting here:

For example:

valueIDs: ( valueID, name ) --key=valueID : here I think you are referring valueID as primary key?

Please elaborate what other keys you are referring to for the following ones:

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

4

The key would be combination of those two columns.

There is no requirement that a key be only 1 column, that's just a destructive myth that too many people believe.

On the PartialStorage, I'm not sure what the best second key column would be. I don't know enough about your data to be sure of that.

But then general rule is:
A child table should have it's first key column the key of the parent table. For the above, valueID is the parent table, the others are child tables.

5

Hi @ScottPletcher ,

So here are the four tables that I came up with based on the discussion with you:

Table1: valueIDs

Key: valueID

valueID valueName
105 CaseOne
106 CaseTwo

Table2: valueIDValues

Key: (valueID,numericalValue)

valueID numericalValue
105 1
105 2
105 3
105 4
106 1
106 2

Table3: valuePartialStorage

Key: (valueID, rowValue)

valueID rowValue columnValue Color plateName Barcode
105 A 1 1 181130KL-RA-17IGI F0155982
105 A 2 1 181130KL-RA-17IGI F0155982
105 A 3 1 181130KL-RA-17IGI F0155982
105 A 4 1 181130KL-RA-17IGI F0155982
106 B 1 2 182230KF-DS-18PXT F0155982
106 B 2 2 182230KF-DS-18PXT F0155982

Table4: valueDetailedStorage

Key: (valueID,selectedValue)

valueID selectedValueID availableCases
105 43 Text1
105 44 Text2
105 45 Text3
105 46 Text3
106 54 Text4
106 55 Text5

I am also little confused for Table 3 : valuePartialStorage as there isn't going to be a unique value in any of the cell as shown above. Does this means we need to split it more?

Thanks I advance for your time!