Work Breakdown structure and normalization

Hi,

I'm working with work breakdown structure codes: 1.0.1.123 or 1.23.32,
etc that seem to be very difficult to sort as text. So I broke them
down and converted them to integers into an array so that 1.0.1.123 as
text became

Array(0) = 1
Array(1) = 0
Array(2)= 1
Array(3)=123

as integer

Then I took the integer array and appended a table with its values and
sorted that way.

So on one table I have

Field 1 Field2 Field3 Field4 Field5
1.0.1.123 1 0 1 123

And Another table I have

Field 1 Field 2 Field3 Field4
1.0.0.123 More pertinent stuff etc.......

Obviously these two tables have a 1 to 1 relationship. Am I violating
any major laws of database design by going this way? Is there a better
way?

Thanks
Paolo

Paolo wrote:
I'm working with work breakdown structure codes: 1.0.1.123 or 1.23.32,
etc that seem to be very difficult to sort as text. So I broke them
down and converted them to integers

So on one table I have

Field 1 Field2 Field3 Field4 Field5
1.0.1.123 1 0 1 123

And Another table I have

Field 1 Field 2 Field3 Field4
1.0.0.123 More pertinent stuff etc.......

Obviously these two tables have a 1 to 1 relationship. Am I violating
any major laws of database design by going this way?

You are breaking a theoretical rule that says you shouldn't store the
results of a 'calculation' (being a concatenation in this case) but in
practice if it makes your SQL code easier write then it's worth
considering. I can see that it would be easier to verify that 1.0.1.123
and 1.0.01.123 are duplicate values with each element parsed out.

However, you must still ensure you have data integrity so make sure you
are not missing the validation rule (CHECK constraint) that ensures
Field 1 is indeed a concatenation of Fields 2 to 5 or the constraint
that ensures each concatenations is unique.

Oh, and in case it isn't obvious, you may want to review your use of
field names, especially where the data element name 'Field 2' changes
its meaning/pertinence depending on the table in which is appears.

Jamie.

--

Thanks Jamie,


Appreciate the response.


Paolo


Jamie Collins wrote:
Paolo wrote:
I'm working with work breakdown structure codes: 1.0.1.123 or 1.23.32,
etc that seem to be very difficult to sort as text. So I broke them
down and converted them to integers

So on one table I have

Field 1 Field2 Field3 Field4 Field5
1.0.1.123 1 0 1 123

And Another table I have

Field 1 Field 2 Field3 Field4
1.0.0.123 More pertinent stuff etc.......

Obviously these two tables have a 1 to 1 relationship. Am I violating
any major laws of database design by going this way?

You are breaking a theoretical rule that says you shouldn't store the
results of a 'calculation' (being a concatenation in this case) but in
practice if it makes your SQL code easier write then it's worth
considering. I can see that it would be easier to verify that 1.0.1.123
and 1.0.01.123 are duplicate values with each element parsed out.

However, you must still ensure you have data integrity so make sure you
are not missing the validation rule (CHECK constraint) that ensures
Field 1 is indeed a concatenation of Fields 2 to 5 or the constraint
that ensures each concatenations is unique.

Oh, and in case it isn't obvious, you may want to review your use of
field names, especially where the data element name 'Field 2' changes
its meaning/pertinence depending on the table in which is appears.

Jamie.

--