Jump to content

How to structure this database?


michaellunsford

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.