Table Design - I want your inputs! 1

[Olavxxx]

Hi,

The thing is that I'm currently programming this sales-system.

The system has like x amounts of tables, I've mostly added to the product-tables, changed queries, views and stored procedures.

Ofcourse, I've also edited asp.net code, etc. Made html input, and so on.

However, one part of the input form, is with checkbox.

Would you move this to another table, or would you add the fields to the same table?

It will be approx 15 checkboxes..
Maybe I should make some dynamic system,where one table:
tbl_options
option_id
option_name
option_value
option_group_id_id_group

and one table:
tbl_option_group
ogroup_id
ogroup_name

Does anyone have any inputs?
My system will have different options, based on one field in the product table (which kind of product it is).

eg. product type a might have option a,b,c
product type b might have option a, c, d

etc.

Would you go for the relational model, or would you add all the fields and rather make it more static in asp.net?

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[vbkris]

my approach would be to have 1 field with all the values in concatenated form...

sample:
Product: 1
Optons: ///a///b///c/// (One column)

Known is handfull, Unknown is worldfull

 

[Olavxxx]

Yeah, that's a good idea.. Then I could make an array out of it and check if the checkbox id is in the arraylist..

Thank you.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[AlexCuse]

Are you going to need to query very extensively on the values in these checkboxes?

Ignorance of certain subjects is a great part of wisdom

 

[Olavxxx]

@Alexcuse:
I will query them per display..
At each hit, it has to display the properties, which then are the checkbox properties or variable values.

On admin, it has to show the checkbox, on other display, only the variable value.

What I have done now, is to make a table:
chkID int Unchecked
chkName varchar(255) Unchecked
chkDefault nchar(1) Checked

This table will be defaulting the values, as well as it will store the name of the checkbox.

I think I'll populate the checkbox on the page, by:
SELECT * FROM tbl_CHK

Then, I'll compare the value in a field in the products table, split it into an array and check if exists in the tbl_CHK array.. If so, enable, else disable.

I think this sollution will give a dynamic possibility, as I can then add more checkboxes to the sytem, by meerly adding a row in the table? As a checkbox only has an id and an integer to show if it's selected, this should be a good idea?

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[AlexCuse]

Ah I see what you are going for I think. Are these something like options to let a web application know how to behave?

I thought that the options in the checkbox were some kind of product option that you may need to query off of, in which case I'm not sure the space savings of having them in a single column would outweigh the costs involved in splitting the string for queries. Sounds like you have a handle on it though. Good Luck!

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Olavxxx]

@alex:
it's a sale system, so it is properties for the product and all products are unique.

However, the properties will mostly be static, but they may change in a year or so..

eg. if I put cars in the system, there could be properties like ABS Brakes, Aircondition, Alarm, etc.

One day, there might be some new amazing invention, called "Autopilot" or whatever.

However, not all cars have Aircondition.
My idea was now to use the table for lookup of checkbox-names and simply store the checkbox-id's in one field, per product.

Another way to go, would be to make another table, which would bind the tbl_chk to the tbl_product. However, I guess that would require one row per product and that might give more strain on the system.

System strain is not very important, not as important as the dynamic behaviour of the system itself. If properties are defined in a table, one can add/delete them later.

I could also then make a table: product_category.
With that table, and maybe one table inbetween (so I dont get a many:many), I could define which properties belongs to which product-types.

When I started out with databases around yr. 1999, I was told to make relations out of everything.

Some years passed and then people said that you should not make relations, if the data is not unique (due to system stress). That was why I was thinking if it is that good to use a 1:many on something as simple as the properties, seeing as they might differ product v.s. product and might change in the future.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[jbenson001]

I think what Alex is getting at is that if you want to qurey on these properties, it will be cumbersome in the structure you have. If they are separet columns it would be easier.

 

[Olavxxx]

@jbenson001

Yes, I know it would be easier to check value of a column, but it would also make it harder to expand the system without database knowledge/access.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[vbkris]

>>I think what Alex is getting at is that if you want to qurey on these properties, it will be cumbersome in the structure you have.

hi J, i dont think that will be an issue as ALL properties are seperated by a ///

therefore if you want to see if one ewntry has the propert "a" then your query would be:

seelct * from table where Propertyl column like '%///a///%'

if you notice ALL the properties will have a /// in the front AND in the back...

Known is handfull, Unknown is worldfull

 

[Olavxxx]

Actually, I'm doing it like so:

Dim arrCheckboxes 
arrCheckboxes = Split(product.chkBoxes. "\\\")

checkThemBoxes(Me, strCheckboxes)

    Private Sub checkThemBoxes(ByVal Page As Control, ByVal chkID As Array)
        For Each ctrl As Control In Page.Controls

            If TypeOf ctrl Is CheckBox Then
               If Array.BinarySearch(chkID, DirectCast(ctrl.ID, Object)) > 0 Then
                    CType(ctrl, CheckBox).Checked = True
                End If
            End If


        Next
    End Sub

It's not working 100% yet, as I have a problem identifying the checkboxes from the checkbox-list.

The issue is not in this function however, but in the function I use to fill the checkboxlist.

eg. I have to change my code, so that when the checkbox-list is filled, what has id = 1 in the db, gets .ID = 1.
This is however not due to the code above, as mentioned, but due to the fact that the function I use for filling my checkboxlist was developed for a different purpose, so I guess I have to make some clone or maybe just make an optional parameter to that function, which will switch the functionality a bit.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[Olavxxx]

Sorry, that should be DirectCast(ctrl, CheckBox).ID), I guess.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[vbkris]

you need not split them AT ALL.

try this:


checkThemBoxes(Me, product.chkBoxes)

Private Sub checkThemBoxes(ByVal Page As Control, ByVal chkID As String)
For Each ctrl As Control In Page.Controls

If TypeOf ctrl Is CheckBox Then
If chkID.indexOf("///" & ctrl.Id & "///") > 0 Then
CType(ctrl, CheckBox).Checked = True
End If
End If
Next
End Sub

Casting is required ONLY when you have to set the checked property to true...

Known is handfull, Unknown is worldfull

 

[gmmastros]

Please read this article.

http://en.wikipedia.org/wiki/Database_normalization

There are many such articles on the internet that discuss database normalization. One of the rules for First Normal Form is: Each attribute must contain a single value, not a set of values.

Going back to your example with cars. Some cars have air conditioning, some have Anti Lock Brakes, some have Power Windows, etc... All cars have some combination of attributes.

So... what Alex was getting at was... You may want to do searches on this table to find cars that have airconditioning and power windows. If you store all of this data in 1 field, then you will still be able to use a where clause to get the appropriate records. However, there will be a lot of string parsing involved, which will be slower than if you had seperate fields. This is especially true if you plan on having many records in the table.

Since you are currently in the process of building this functionality, I strongly encourage you to do it the right way, following database normalization rules.

Here's how I would do it...

I would have a table for each property.

[tt]
Properties
PropertyId Description
---------- -----------
1 Air Conditioning
2 Power Windows
3 Anti Lock Brakes
[/tt]

And another table that associates each car with it respective property.

[tt]
CarProperties
CarId PropertyId
----- ----------
1 1
1 3
2 2
2 3
[/tt]

Notice how car 1 does not have a PropertyId of 2, this would mean that car 1 does not have power windows. Car 2 has 2 & 3, but not 1, so... No Air Conditioning, but power windows and anti lock brakes.

With this system, you can easily add additional properties (like AutoPilot) without having to change the structure of the tables. Also, since each car can have multiple properties, the CarProperties table will be the larger table in the system. With this design, the largest table has 2 integer columns. Integer columns take less space than your varchar column idea, and integers are ideal for linking tables together, giving you the best performance.

I hope I've given you some things to think about. Whichever path you decide to follow, I wish you luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom