Hi,

I am interested in opinions on the following,

main(product_id, short_text, price, type)
productTypes(type_id, type)
productTables(type_id, tableName)
productA(product_id, field1, field2)
productB(product_id, field1, field2)

To retrieve all products of type 'A', one must know the table name, in this case 'productA'.

Here is one method.

Create a table that contains the table name that corresponds to each product type, thus the stored procedure only needs to recieve the type_id which can be used to obtain the name of the respective table.

I guess the "Re: Boring" title means I should respond

Well, this is an interesting idea - sounds like something a C programmer would come up with - a mixture of data and metadata.

I'm not sure how much is gained though. Presumably tables ProductA and ProductB would in reality have a different set of columns - otherwise, what's the point of the separate tables? So your stored procedure cannot use dynamic SQL, instead it will have to do something like this:
That is no better than
Next I suppose you could suggest having another "metadata" table like this:
Then you could create the SELECT statement dynamically. But then what would you do with the variable number and types of values returned? If feel another IF statement looming...

that's the part that i don't understand too

if they look the same, they should be combined

if they look different, the whole scheme is wonky

Indeed, they would be different.

master(id, short-text)
productA-Book(id, book-specific-attributes)
productB-DVD(id, dvd-specific-attributes)

I would like to point out that I do not especially favour the method I supplied, rather am hoping for someone to point out that it is not required.

The problem I am trying to solve is related to the Data Access components of an application. Granted, application design does not determine database design.

Well, my post tried to show that in this case the method doesn't buy you anything - you still need separate code for each table type, whether you store the table names in another table or not. Something that creates extra work without any benefits is not required, right?

ah, books and dvds, that's a much better example

this is a rather common data modelling problem (subtypes and supertypes)

the place from which to consider your design options is the application

the application may ask "show me all things" but under the covers, the logic must retrieve and format the results for books and dvds separately

for example, an application screen may list several lines showing all books and dvds in inventory, but the lines listing the books are formatted differently from the lines listing the dvds

so the app already has to have "if" logic in it

one way around this is a UNION query, but that forces unlike data to share the same columns, which you could also achieve by simply squeezing both types into a common table to begin with

to me, the subtype/supertype model is easiest with a single table, many common columns, a few specific columns to each type

e.g. name, title, published, datepublished, inventoryquantity, bookcover, dvdformat

there is still some "if" logic required, but it's not as bad as separate tables

consider what searches (e.g. search all inventory for "planet of the apes") would look like in the multiple-table model versus the single-table model

lots of examples where the single-table model is better

and very few where the multiple-table model is better

finally, let's talk about nulls

the bookcover column would be null for dvd rows, and the dvdformat column would be null for book rows, right?

pretty neat, eh?

Rudy, excellent. However, do we now advocate denormalizing the data all into a single table to make the application's task easier. This is the other debate. An application with N methods or one denormalized table to assist the application.

On a point of order here, it would not be "denormalised" - since every non-key value will be dependent (solely) on the key, and column values are still atomic.

I think Rudy has already spelt out the pros and cons. I agree wth him: a single table with some columns being populated only for certain subtypes is the easier approach, though less theorerically "pure". That is, provided there are not so many subtypes that you end up with an unweildy 1000-column table. But then, if there were that many subtypes, handling a table for each would be a nightmare too...

If you do have hundreds of subtypes, I'm not sure that either approach is satisfactory. Perhaps in that case a rethink is required: is it practical and necessary to break down the data so finely? We would be talking about 100s of variations on the input form, 100s of variations in reports, etc. Maybe that would be time to say "enough: we will hold all the common attributes, plus a few frequently used ones, and then give you a big text field in which to record other details."

Luckily, I have never been in this situation, but I have seen many posts about it.

This is the exact problem I have. I think i'm going to stick with many small tables rather than one BIG table with all the columns, for some reason it doesnt seem right to have redundent columns for subtypes.

If you guys come up with a brain wave, I'd really like to here about it. I'm punching walls on this.

Jwab, I would not say that the columns are redundant. All the columns would be required, just not applicable to each row. To expand on my original method:

master(id, search_text)
types(type_id, select_view_def)

Then (simplistic example),

1. Select distinct view_def from types
2. Retrieve appropiate data from each table using master.id

The only reason I thought of this was to avoid a possible situation that consisted of for example, 15 sub types with a total of 60 attributes.

i have a great suggestion

put together a data access strategy for each model (one supertable with columns for all unique attributes, some of which will be null on some rows, versus multiple subtype tables, none of which will have any nulls)

consider the books and dvds example

here's the gedankenexperiment, and i specifically want you to think about what code would be necessary to pull this off --

present an inventory list of books and dvds in title sequence

the code involved will be a combination of sql and application scripting/logic

remember, it's got to be in sequence by title, and yet be a mix of books and dvds

think about the sql (one table versus many) and also the application logic

fun, eh?



bottom line: unless i have ample reasons not to, i'm gonna go with the single table approach every time

a single query, and you can do the ORDER BY easily

with multiple tables, you either have to "combine" the queries with a UNION, or else retrieve them separately and (ugh!!) sort them in your application script

you see, data modelling is not entirely about "the best structure"

you also gotta get the info back out easily

Ok I see how it would be so much easier to extract infromation from the db with only one table looking after all the different types of products.

BUT with my problrm I think an alternative method may need devising. You see your single table idea is probably ok of the DVD, Book scenario, because you dont need to track each indiviual DVD or book. With the mobile phone problem, we need to record each individual mobile phone. So instead of having a product list of a few 100, or even a few thousand. This db needs to track a few 10's of 1000's which increase's every day, so in 2 years time, where we still need to know where a particular mobile is, the table could have nearly 100,000 rows,which will increase to millions as the business grows.
This would create one MUMMA of a single table. So what performance hit are we taking in the select, and update routines.
If your certain that the single table is still the way togo, then I will redesign my scheme (after all, I need to get this right).

On a slightly alternative approach, I've been reading a bit on OO db design. It's abilty to handle real world entities and recursive queries is appealing.
Although I'm still not sure to the full extent or it's usefulness.

I've just jotted this down, but if I went the single table route this is what it would look like.


phone/sim(stock_code, make, model, IEMI, sim_num, mob_num, cost_price, supplier, location, state, date_in, date_out, date_sold, cust_id, staff_id, tariff, price_paid, RA_num, rtn_date, discon_date, con_err_date, resell_date)

That is all the information required in the life of a phone or SIM. From that other tables would be generated, for sales, commissions etc

There will be alot of data replication for fields such as stock_code, make, model, price, location, state, dates...

but maybe this doesnt matter, just gotta make sure fields have lots of constraits when changeing a record.

Is this an applications for super/sub typing or not?

The row count of a table is not an issue. My original point was to devise an alternative for a situation where the total column count would exceed, for example 50 columns.

I don't know which DBMS you (jwab) will be using, but some allow you to "partition" tables to deal with concerns about managing large tables. Thus you may get physical separation of phones from SIMs (for example) without having to have logical separation.

Having said that, a few million rows is not considered alarmingly "big" for an enterprise DBMS like Oracle, SQL Server or DB2 these days.

column count doesn't matter either, if the table is normalized

oh, wide tables may not perform as well as related narrow tables, but that isn't the issue here

a good DBA can take a wide table and make two 1-to-1 related narrow tables, such that you (the user or application) would never know the underlying physical data structure was actually two narrow tables being joined

that's within the realm of physical implementation and can be done later for performance reasons

would the DBA attempt to consolidate a subtype/supertype structure into one signle table (or two narrow ones if the single one is too wide)?

probably not, no DBA that i ever met would've dared it