Jump to content

Archived

This topic is now archived and is closed to further replies.

michaellunsford

How to structure this database?

Recommended Posts

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.

Share this post


Link to post
Share on other sites
I'm not sure that I really understand how the product "skins" relate to the items/calendar table.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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";
}

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Hmmmm that is hard to think of... Ill try to think of something but for now Imma go to bed..

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
I still think having product groups is a much better idea, and doesn't require duplicating any information.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
[!--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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.