Hi there,
I raised this question before but was told my explaination wasn't very
clear. So hopefully this will be easier to understand.
In a stock control system:
I have two models, Item and Supplier. Item HABTM Supplier and Supplier
HABTM Item. This uses the following tables:
items:
id | description | attribute1 | attribute2 |
etc.... | etc..
suppliers:
id | name | address | contact |
phone_number | etc..
items_suppliers:
item_id | supplier_id
For each supplier I'd like to be able to keep track of how long it will
take them to deliver a particular item. This way, when I select an item
I can see which supplier will be able to deliver it the fastest.
Therefore, I keep this information in the items_suppliers link table,
along with item_id and supplier_id. I also include fields for the
supplier price for the particular item and the amount of this item I
have in stock from this particular supplier
e.g.:
item_id | supplier_id | supplier_price |
delivery_time | num_in_stock
However, there may be a situation where I already have e.g. 10 of
item_id 1234 from supplier ABC at $5 each and I get 20 more of the same
item from the same supplier at a different price.
So my link table would now look like this:
item_id | supplier_id | supplier_price |
delivery_time | num_in_stock
1234 ABC $5.00
24 Hours 10
1234 ABC $5.75
24 Hours 20
I'm thinking I should do one of the following:
(a)
Extend the Primary Key of the link table to include the supplier_price
as well
as item_id and supplier_id {i.e. Primary Key = (item_id, supplier_id,
supplier_price)}. However this will mean duplicating the
delivery_time data every time a new supplier price is given. I'm also
wondering if extending the Primary Key like this
will affect Cake's understanding of the Model.
OR
(b)
Create a third Model called Stock which has the following:
item_id | supplier_id | supplier_price |
num_in_stock
leaving the items_suppliers link table as:
item_id | supplier_id | delivery_time
This avoids the duplicating the delivery_time data for every entry but
adds the extra table,
and therefore duplicates item_id and supplier_id.
OR
(c)
I could just put 'supplier_id', 'supplier_price' and 'num_in_stock'
back into the 'items'
table and extend the Primary Key to include supplier_id and
supplier_price
giving me the following:
{i.e. Primary Key = (id, supplier_id, supplier_price)}
So the 'Items' table would now look like:
items:
id | description | attribute1 | attribute2 |
supplier_id | supplier_price | num_in_stock | etc....
| etc..
However, again, I'm wondering if extending the Primary Key like this
will affect
Cake's understanding of the Models.
Which method would be most efficient?
Any thoughts would be greatly appreciated.
Cheers,
Sonic
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/cake-php
-~----------~----~----~----~------~----~------~--~---
