How to structure this database?

[michaellunsford]

I've been working with a rental company on this project for years -- all of a sudden we've got a product with three different "skins" that they're renting like three different products. Catch is, if one gets rented, the other two become unavailable.

This portion of the site is controlled by just two tables: items and calendar (which records item numbers and the day they're "unavailable" on).

I've been trying to figure out how to improve the existing system to handle aliases -- but I'm coming up blank. Any ideas would be greatly appreciated.

[fenway]

I'm not sure that I really understand how the product "skins" relate to the items/calendar table.

[michaellunsford]

The product database has 15 items in it. The client just purchased an item that is represented as three separate items. Making the database have 18 item records. Problem is, those three items are all the same thing. So if one of the three gets rented, the other two cease to be available.

After a few months of "thinking about it" I am still coming up empty on ideas.

[corbin]

set all 3 as unavailable when one becomes unavailable maybe? Or tell the php script...

if(($p1av == "false") || ($p2av == "false") || ($p3av == "false")) {
$p1av = "false";
$p2av = "false";
$p3av = "false";
}

[michaellunsford]

Yes, if one unit becomes unavailable, all of them are unavailable.

Since they're likely to add more units and aliases in the future, I'd rather not hard code specific units in PHP. The next likely scenario is they will buy another identical unit and another few skins...

So, for discussion sake, lets say they now have 2 units and 5 skins. Clients can now choose from 5 total units -- BUT, when two of the five are rented, the other three are no longer available (because they're just skins of the two 'real' units).

Does that make sense?

right now, I have the database setup with two tables:

Table 1. keyfield, unit_name, description, price, alias_of
Table 2. keyfield, unit_keyfield, date, customer_name, customer_address, customer_phone, customer_email, notes

so, in the units category, the alias_of actually records if this is a skin, or a real unit. the problem is, I can't figure out how to make all three units unavailable after one is rented.

[corbin]

Hmmmm that is hard to think of... Ill try to think of something but for now Imma go to bed..

[fenway]

[!--quoteo(post=369698:date=Apr 28 2006, 03:26 PM:name=michaellunsford)--][div class=\'quotetop\']QUOTE(michaellunsford @ Apr 28 2006, 03:26 PM) [snapback]369698[/snapback][/div][div class=\'quotemain\'][!--quotec--]
The product database has 15 items in it. The client just purchased an item that is represented as three separate items. Making the database have 18 item records. Problem is, those three items are all the same thing. So if one of the three gets rented, the other two cease to be available.
[/quote]
Well, this is of course the problem -- by storing it as 3 separate items, you lose the relationship between them. Why not hack this is... create a single item that represents the group, and then add another table with the three skins linked back to the original group. That way, you can mark your "group" as rented, and handle which one they picked elsewhere. Depending on your DB design, there are a number of ways to do this.

[michaellunsford]

Okay, I create an alias table (that contains the 18 'perceived' units) and point to the 'real' unit in the original table (which seems rather redundant).

units_table:
keyfield, name, description, price
aliases_table:
keyfield, name, real_unit (which is the keyfield from the `units_table` table), price

This would be rather easy to do -- just post the entire alias list as the product list, and grab availability from the real_unit field.

Now, how do I prevent entry of new items from being redundant.... hmmm...

select box of all real units (created dynamically from the real units table) with the last option being "new base unit" -- which will INSERT the new unit into both the real units table and the aliases table.

it's crazy, but it just might work. Thoughts?

[fenway]

I still think having product groups is a much better idea, and doesn't require duplicating any information.

[michaellunsford]

I hate to ask you to elaborate -- but I just can't get my mind around it.

thanks!

[fenway]

Sure... if you have only one record per "product" in your table, then marking it as active becomes trivial -- you don't have to worry about multiple skin options at this level. However, because you have configurations, you store a product_group_id, a FK from another table, and then you have yet another table where you link product configurations (your "skins") to product groups.

Does that make sense?

[michaellunsford]

[!--quoteo(post=370298:date=May 1 2006, 11:02 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 1 2006, 11:02 AM) [snapback]370298[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Sure... if you have only one record per "product" in your table, then marking it as active becomes trivial -- you don't have to worry about multiple skin options at this level. However, because you have configurations, you store a product_group_id, a FK from another table, and then you have yet another table where you link product configurations (your "skins") to product groups.

Does that make sense?
[/quote]


okay, so in the main product table, I just add another field for a group ID. But you lose me after that. It looks like I'm going from one table to three?

[fenway]

Yes, that's basically correct -- instead of storing a product uid, you store a group uid instead, and then you need tables both for the groups themselves, and then to the underlying products.