flexible database design and complex queries

[miahmiah900]

hello... i wondering how to design a database that needs to be flexible. it is not a simple case of "i know the fields that will be needed, lets make columns for them", but rather there may be 0 or many different columns in a given table... what i want to know is the best way to design a database like this, and how to write queries for selecting data from them.

any comments or pointers to good database design resources would be greatly appreciated

let me explain in a little more detail an example situation. i want to know if there is a better/easier way to do this...

take the example of a product database that could potentially have 4 tables: tbl_product, tbl_option_name, tbl_option_data, tbl_option_key, and tbl_inventory.

tbl_product stores names, descriptions and image paths related to each product (a candle, for example). tbl_option_name has different types of options associated with a product (candle color or size). tbl_option_data contains the various choices for an option (red, tall). tbl_inventory keeps track of distinct types of inventory and their current quantity, of which there can be 1 or many for each type of product. tbl_option_key associates particular choices of options with an entry in tbl_inventory.

with this design, i can create unlimited number of options for a product, with unlimited number of choices for each option. thus i can describe virtually any type of product and make it available for sale to customers as a single product with choices for customizing it rather than a collection of many similar products.

but, is it efficient? to use this design, i had to write multiple queries to find all the information regarding a single product (although maybe with the new versions of mysql and subqueries it would be possible). also, i cannot figure out a way using an sql query alone to find a particular entry in tbl_inventory when given only a tbl_product id and option id's (from tbl_option_name and tbl_option_data).

what i have had to do is write some php code with some loops to discover which inventory is being described by the options that have been selected.

thanks for reading!
- miah

p.s. i know oscommerce is available for use, but i am posing this as more of a question in database design and not as seeking help with the development of an actual application.

 

[sleipnir214]

I don't know why you'd need more than one query to pull up any product's information, unless you haven't made it possible to join the tables.

And unless your inventory is more complex that you describe, you don't need the complexity you've shown. For example, unless a particular model of candle comes in red, white, and blue, there is no need to store the color as an option. If all model 123 candles are red, red is not an option -- it's an intrinsic part of the product.

But let's say you have two t-shirts:
T-shirt model 234, which has the MySQL logo on it is available:
in three sizes, M, L, and XL, and
in three colrs, red, white, and blue.

T-shirt model 345, which has the PHP logo on it is available:
in three sizes, L, XL, and XXL, and
in three colors, purple, green, and gold.

Now, you want to not duplicate any color or size in your database. (An XL t-shirt is an XL t-shirt. A red t-shirt is a red t-shirt.) For this, you need to store the options ("Color", "Size&quot and attributes (red", "XL&quot in another table. Then you want a third table to relate those to together, so you can know that "XL" is an attribute of "Size", etc.

Now, you need your product table, plus an product_options table and a product_attributes table. These tables describe which options apply to which product and which attributes are available for an option for a product.

But here's the kicker. It's possible, by properly relating all the tables, to pull all the data for a product out of the database in a single query. It's also


You've mentioned OSCommerce. It's open-source and it has a very good database schema for storing this kind of data. I recommend you take a look at how it's done there.

Heck, OSCommerce is internationalized, so a description of a product is stored in a table separate from the product itself -- so that you can store the same description in multiple languages.

In OS commerce, "size" is an option, "XL" is an option value, and the combination of the two, in relation to a product is a product attribute.

Want the best answers? Ask the best questions:

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

TANSTAAFL!!