Database Design

[slindstr]

So let's hypothetically say that I have a php page with a bunch of drop down menus, one has color choices, one has sizes, and one has shapes. I want a user to be able to select one of each, but here's the catch - I want the options to come from a database so to add or remove options all I have to do is do it once in the database. No big deal, but my question is what is the best way to do this?

The way I see it is something like this:
- 3 different tables, one for size, one for shapes, and one for colors
- Each table has 2 columns, one for an identifying integer, and one for the value that is being identified.
- on the actual php page the OPTION tag values would be the identifying integers and then it would display the values i.e.

<option value="1">Blue</option>

The problem I see with this is that you would have to hit the database 3 times every page load just to display the drop downs...when you get to a bigger project using this method with more drop downs and more users wouldn't this bog the database down? Is there a better way to do this?

Thanks
Steve

[ahundiak]

The fact that you are generating three simple queries will probably not be a big deal. Premature optimization can really slow project development. Keep in mind that databases will automatically cache these sort of queries so the overhead will be low.

However:
Do all users get the same options? And are the options infrequently updated? If so then just use a list in your code and don't worry about the database.

Or:
You can use a little cache of your own. Take a look at Zend_Cache (framework.zend.com) for an example. But again, it's doubtful your application users will notice anything.

[slindstr]

well that was just an example - the site I'm building has 11 drop downs like that, but I still think that wouldn't be very noticeable.

Thanks for the link - I'll check it out first thing tomorrow (well I guess technically today since it's like 1am 🙂 )

[leatherback]

Hi Slindt,

I would add one table for each drop-down, IF you will have a limited number of drop-downs, OR if these dropdown-tables are also used as a lookup for your main tables.

I have used a similar setup for a management module, where users are allowed to add options to the drop-down menus. I wrote two functions on top of that: One functions get_table, which will get all values from a table, sorted by the first colum.

The second will use this data and build the core of a dropdown. (I cannot find the get_table right now, but this coul dbe very easy, see example below; the dropdown is below. Hope it helps)

Get table like this:

function get_table($table)
{
$query = "select * from $table ";
$result = mysql_query($query);
$array = array()
if($result)
  {
  while($row = mysql_fetch_array($result))
    {
    $array[] = $row;
    }
  return $array;
  }
else
  {
  return false;
  }
}

// generic function to create a dropdown body from an array
// $array: input data, each array element represents information about one dropdown enrtry, in an sub-array
// $selected_id: Identifier of the selected drop-down element
// $value_label: The name of the array-element that holds the value for the dropdown
// $name_label: The name of the array-element that holds the name for the dropdown element


function create_dropdown($array, $selected_id, $value_label, $name_label)
  {
  $dropdown = "";

  foreach($array as $key=>$value)
    {
    $selected = "";
    if($value[$value_label] == $selected_id)
      {
      $selected = "selected";
      }

$dropdown .="<option $selected value=\"".$value[$value_label]."\">".$value[$name_label]."</option>
            ";
}

  return $dropdown;
  }

[slindstr]

Thanks for the reply - I actually have similar code, but I guess my real question is how willl this method of implementation affect performance? Will it be terribly slow with a lot of users? And how many would "a lot" of users be?

[Piranha]

I wold like to say that it is all up to how you use the information from that table in the rest of the database. Let's take an easy example, you sell paper. The paper could differ in size, color, brand and thickness. Now if you only want to use the information in drop-down lists then both possibilities would work.

But if you want to connect that to the specific type of paper, for example to be able to order it from the manufactorers, then I suggest that you do it like this:

table product
id

table properties
id
property_type
value

table product_properties
product_id
properties_id

If you try to do this with one table for each type of property then you will get in trouble when you want all properties for one product. With the paper example you would have to join 5 tables (and more with more properties). But the way I showed you it is always 3 tables that need joining.

Personally I would make it in one table as above, then you can use different queries for the different menus anyway: SELECT value FROM properties WHERE property_type = "color". Not much extra work, but a lot easier to expand or change the queries if needed later on.

[Roger_Ramjet]

Many people make the mistake of having properties tables with 2 columns, id and text. In general such table will only have a limited number of rows so using an indexed id column will be slower as it required 2 disk reads: first to read the index into memory then to read the table.

Definitely do not make the mistake of having 1 table with multiple property types in it as this now means 2 indexes to parse and 3 disk IOs. It also makes it confusing for non-technical staff to maintain.

Just make the properties table with 1 column and index that. When you update the table, optimise it to sort row storage into index order. When you do need to use the table data, mysql query optimiser will dispense with the index and just read the table direct as it can suck the whole table up in one disk IO.

Now you will be storing the data rather than an id in the main table so joins are elliminated. OK it means a few more bytes of disk storage but not much unless your properties run to 30 or 40 chars; personally I have them limited to 10 or 12 anyway so that parsing the index will not be onerous either when you do need it.

All around this strategy balances out and has had no noticeable performance impact on databases with 500K+ records in the main table: what you loose in text indexes you gain in elliminated joins.

You can if you want prefix each with a unique character in the order you want for presentation

a New
b Confirmed
c Cancelled
d etc