schema style question

pnoeric

Hi all, in the spirit of the current cover story on PHP coding style and the archive piece on normalizing db tables, I am interested to hear your thoughts about the db schema I'm working on.

I have a table that stores information about widgets. For each widget (that is, for each row in the table), I store a number of attributes about the widget, including where it is located... the location can be either a URL or an ID number referring to an internal WidgetHolder(tm).

I am thinking about the best way to keep that info-- here's the three 'obvious' options. What do you think?

Each widget gets a row in a table; the table has two columns (URL and ID) to describe the location-- so one of those two columns is always null in each row. Pros: easy to retrieve all of one type of link, since I can select just the rows with data in one of the columns. Cons: guarantees a wasted cell in every row.
Each widget gets a row in a table; have one column for the location which I parse each time I retrieve ("is the column_location an ID number or a URL?"). Pros: most compact storage method. Cons: overhead of processing each time I want to know how to find a widget.
Similar to 2, except the type of widget location is stored as a separate column in the table. Pros: same as option 1 above. Cons: extra processing overhead when inserting rows; extra column seems extraneous.
Each widget gets a row in a table for all the attributes except location. A separate table called wheres_my_widgets has three columns: one for the widget number (to relate it to the first table), and two columns for location, similar to option 1, above. Pros: easy to select the widget location from the separate table; breaks out sloppy scheme from main widget table. Cons: separate table; wasted cell in every row.

My goal is graceful design with an eye towards speed; if there is any penalty I'll take, it's when a row is being inserted.

What do y'all think?

-Eric

Anon

number 3 is your best bet I think.

[deleted]

I agree, option 3 would be the most easy to use for selects.

A note:

Don't worry about "wasted fields" as they take up only a few bytes of space, and have no negative effect on speed.

If you compare 1 and 3, 3 would probably take up more space than 1, because in 3 both fields are allways filled.

smarlowe

Number four, but with two join tables might be best.

Have a widget table, a widget_url, and widget_store table.

Then, if the widget is store at an URL, you use it's id in the widget_url table, and a simple join against that table returns just the widgets with url storage. Same for the store table.

select * from widget, widget_url where widget.id=widget_url_wid;

Since the relation is formed by the widget id being the in the secondary tables, if (or when???) you need to keep track of both a web page AND a warehouse location, you can do both easily with no changes to your database.

steeler

Aren't joins slower than plain selects though ?

pnoeric

All - thanks for taking the time to reply, it's always rewarding to bounce things off the community...

Scott - so the location is completely removed from the widget table and is completely stored in one of the other two tables; retrieval is keyed off the widget_id... got it... that seems by far the cleanest, though a slight speed penalty on retrieval (as Sam pointed out). For this specific app, I don't think it'll ever get to even 100k+ rows, so that is probably the 'right' answer. Thanks!

best,
Eric Mueller
http://www.themepark.com/php/

smarlowe

With judicious use of indexes, the performance should be within a few percentage points either way really.

Anon

Yes, but sql databases are slower than dbm style text databases. Speed isn't the only consideration when designing a dbms driven site, maintenance of code and data are important too, and the "one big table" theory makes that very difficult to do.

On my box at work, a pair of properly indexed tables will join faster than a simple select can run on a single unindexed table by about 3 or 4 times, and the difference if both are indexed is less than 5%. Your mileage may vary based on which database you are using and how good it's query optimizer is.