MySQL Database Design

[GWHicks]

I am creating a database to house information for my work. There are a couple different topics that each have a handful of options in them. In most of the topics more than 1 option can be chosen.
Would my database design be better to have EVERY option as a field, and if so should I make those fields store binary result or actually store the text for that option?
Or would I be better to make a single field of the whole topic, compile all checked options into a single variable. And then store the value of that variable in the field?
I hope I have explained this enough. I am using PHP and HTML forms to store and retrieve the data so keep that in mind when recommending database structure. Thanks!


Greg Hicks
VB.Net (Newbie) Programmer

http://www.ajlb2.com

 

[sleipnir214]

If the number of Y/N choices that make up an attribute's possible values will never change, you can create a table with columns to store each value. Also, if the number of attributes is less than 64, you could also use a MySQL SET column to store the values in a single column (

http://www.mysql.com/doc/en/SET.html).

If the number of attributes will change over time, either through replacement, addition, or deletion of values, this way may not be the best. Every time you change the values, you'll have to be editing your tables. Or you will have to reserve unused space or SET options in your table that you will only fill after time.

You could also use a related table to store the attributes.


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[GWHicks]

You can see an example of my original form at

http://www.hickscomp.com/quote/createquote.html

and you can see what I am trying to save. Let me know if your idea works for something along those lines.
I will have to post that later into another document (currently done in word, but I will probably format it using HTML so it is web viewable) and there will be a listing of topics:

P-20 Core and Cavity
H-13 Hydraulic Slides
3-Plate
Hot Runner
Reverse Injection
Open and Shut
Parting-Line Locks
etc.

That will be the main body of the letter created, so I need to pull information such that I can get it into this format without a lot of hassle later.

Thanks again for the tips!


Greg Hicks
VB.Net (Newbie) Programmer

http://www.ajlb2.com

 

[sleipnir214]

I see nothing on your form that goes against my original advice. The real question of which database schema really depends on how static the selections are.

For example, will your company only provide P20 Steel, H-13 Steel, 420 Stainless Steel, and 7075 Aluminum in the "Core and Cavity" attributes? If you will never provide anything more than those, then a SET-column or group of columns is not inappropriate. If, however, you will be adding more materials to that list, you should be using a related table to store the value(s).

I don't know, either, from where you are getting your values. If the available attributes are hard-coded, I strongly recommend you make the form database-driven. That way, instead of modifying code to add a new material to "Core and Cavity", you just add a new row to your "c_and_c_materials" table.

I also notice that you have HTML form element names that don't lend themselves well to programmatic use.

For example, your "Core and Cavity" form element names:

<input type=&quot;checkbox&quot; name=&quot;P20CORECAV&quot; Value=&quot;P20 Core and Cavity&quot;>P20 Steel<br>
<input type=&quot;checkbox&quot; name=&quot;H13CORECAV&quot;>H-13 Steel<br>
<input type=&quot;checkbox&quot; name=&quot;420SSCORECAV&quot;>420 Stainless Steel<br>
<input type=&quot;checkbox&quot; name=&quot;7075CORECAV&quot;>7075 Aluminum<br>
<h4>Other Cavity</h4>
<input type=&quot;text&quot; name=&quot;OTHERCAVITY&quot;><br>
<h4>Other Core Type</h4>
<input type=&quot;textbox&quot; name=&quot;OTHERCORE&quot;><br>

will produce input in $_POST like:

Array
(
    [P20CORECAV] => P20 Core and Cavity
    [H13CORECAV] => on
    [420SSCORECAV] => on
    [7075CORECAV] => on
    [OTHERCAVITY] => 
    [OTHERCORE] => 
)

However, if you change the element names around a little:
, you can make the values a lot easier to get to in PHP:

<input type=&quot;checkbox&quot; name=&quot;CORECAV[P20]&quot;>P20 Steel<br>
<input type=&quot;checkbox&quot; name=&quot;CORECAV[H13]&quot;>H-13 Steel<br>
<input type=&quot;checkbox&quot; name=&quot;CORECAV[420SS]&quot;>420 Stainless Steel<br>
<input type=&quot;checkbox&quot; name=&quot;CORECAV[7075]&quot;>7075 Aluminum<br>
<h4>Other Cavity</h4>
<input type=&quot;text&quot; name=&quot;CORECAV[OTHER][CAVITY]&quot;><br>
<h4>Other Core Type</h4>
<input type=&quot;textbox&quot; name=&quot;CORECAV[OTHER][CORE]&quot;><br>

then in PHP, the input will look like:

Array
(
    [CORECAV] => Array
        (
            [P20] => on
            [H13] => on
            [420SS] => on
            [7075] => on
            [OTHER] => Array
                (
                    [CAVITY] => 
                    [CORE] => 
                )
        )
)

This latter way better lends itself to being able to traversing input through looping. This can be particularly useful of the values inside the brackets are table indeces from your database.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[GWHicks]

WOW! Every time I think I am starting to get my head around some of these concepts I either ask a question, or come across an article that shows me just how much I don't know! Your response is so full of information I am now debating my current structure even more.

I realized the issue you bring up with the programmatic use of my code and had added value= sections to each of my blocks so I am storing the data exactly as I need in the database. The flexibility issue I was trying to deal with using the OTHER options on each of my categories. If it is of a different type of steel they should store the information using the OTHER box.

I am trying to get confirmation from my boss in each of those categories which ones would ONLY be able to have a single response to them. For example if I were to break up the Core and Cavity section into seperate sections, one for Core and another for Cavity, I could then minimize my database to 2 fields versus the current 5 or 6 options that are there now. I could then use a simple CORE field name with the response used for the core. Most both are made from the same material, but that is not a hard and fast rule (as are most of the items on the list). The varieties and variables are almost limitless which is why I started to structure the table for a fieldname for each possible response. But I can see the potential for that to get out of hand very quickly and for the database size to grow obnoxiously.

However, another possible work around would be for me to incorporate an archival option. Split old quotes out from the main database into seperate files for each year or whatever breakdown we determine to be best. then just build some files to allow searching of those old records and we would then end up eventually with a handful of files, but none that are unmanageable.

Thanks once again for your input, I will take all of that into advisement and examine the database schema further still to try to minimize future issues as much as possible.

Greg Hicks
VB.Net (Newbie) Programmer

http://www.ajlb2.com