Jump to content


Photo

How to structure this database?


  • Please log in to reply
12 replies to this topic

#1 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 28 April 2006 - 02:27 PM

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.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 April 2006 - 06:17 PM

I'm not sure that I really understand how the product "skins" relate to the items/calendar table.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 28 April 2006 - 08:26 PM

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.

#4 corbin

corbin
  • Staff Alumni
  • Advanced Member
  • 8,129 posts

Posted 29 April 2006 - 06:01 AM

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";
}
Why doesn't anyone ever say hi, hey, or whad up world?

#5 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 29 April 2006 - 06:19 AM

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.


#6 corbin

corbin
  • Staff Alumni
  • Advanced Member
  • 8,129 posts

Posted 29 April 2006 - 06:48 AM

Hmmmm that is hard to think of... Ill try to think of something but for now Imma go to bed..
Why doesn't anyone ever say hi, hey, or whad up world?

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 April 2006 - 08:43 PM

[!--quoteo(post=369698:date=Apr 28 2006, 03:26 PM:name=michaellunsford)--][div class=\'quotetop\']QUOTE(michaellunsford @ Apr 28 2006, 03:26 PM) View Post[/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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 30 April 2006 - 01:55 AM

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?

#9 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 April 2006 - 05:45 PM

I still think having product groups is a much better idea, and doesn't require duplicating any information.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#10 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 30 April 2006 - 07:14 PM

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

thanks!

#11 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 01 May 2006 - 04:02 PM

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?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#12 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 01 May 2006 - 05:00 PM

[!--quoteo(post=370298:date=May 1 2006, 11:02 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 1 2006, 11:02 AM) View Post[/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?


#13 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 May 2006 - 06:29 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users