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?
1. 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.
2. 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.
3. 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.
4. 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
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?
1. 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.
2. 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.
3. 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.
4. 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