Show of hands, best solution...

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Show of hands, best solution...

Marc Pinnell-3
I have a current solution that allows branch offices of a single  
corporation to order printing (business cards, envelopes, letterhead,  
etc) and other promo items. Not all branches have access to the same  
set of products. The current solution adds a column to the "items"  
table for each branch (and removes said column if branch is removed).  
Basically just a yes/no column. Is this the "best" way to accomplish  
this or would it be better to have another table (with two columns,  
part number and branch id) that would have an entry for each part  
number and each branch (ie 20 records for part #1111, representing  
the 20 branches that can buy this part)?

Hope this makes sense, I am about to build (copy) another solution of  
similar scope for the same client and wonder if my current solution  
is going to cause trouble down the line.

Thanx for any advice.
Marc

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Show of hands, best solution...

Fletcher Sandbeck
On 5/8/06 at 8:34 AM by [hidden email] (Marc Pinnell):

>I have a current solution that allows branch offices of a single  
>corporation to order printing (business cards, envelopes, letterhead,  
>etc) and other promo items. Not all branches have access to the same  
>set of products. The current solution adds a column to the "items"  
>table for each branch (and removes said column if branch is removed).  
>Basically just a yes/no column. Is this the "best" way to accomplish  
>this or would it be better to have another table (with two columns,  
>part number and branch id) that would have an entry for each part  
>number and each branch (ie 20 records for part #1111, representing  
>the 20 branches that can buy this part)?
>
>Hope this makes sense, I am about to build (copy) another solution of  
>similar scope for the same client and wonder if my current solution  
>is going to cause trouble down the line.

It's better design to have a join table which stores the relationship between branch offices and items.  It makes it a lot easier to add and remove branch offices if you don't have to alter the schema of your databases in order to do so.  The scheme you have works well if you have just a few branch offices, but gets pretty unwieldy as the number of offices increases.

I worked on a site that used a similar scheme for categories.  Originally there were three or four categories and using a field for category inclusion worked fine.  However, the site was redesigned to emphasize more categories and pretty soon there were a couple dozen.  Adding a new category became a chore involving changing the schema of several tables, adding several format files, etc.  We rewrote it to use a categories table and a join table.  Adding a new category takes a few seconds on a Web form, the inlines are simpler (but do use JOINs), and the site is much faster.

[fletcher]
--
Fletcher Sandbeck                         [hidden email]
Director of Product Development       http://www.lassostudio.com
OmniPilot Software, Inc.                http://www.omnipilot.com

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Show of hands, best solution...

Mark Palmer
In reply to this post by Marc Pinnell-3
You could add a column to the items table that contains a integer
representation of a binary value - the set bits of binary value being the
branches that take that item.

So if branches 1,2,5,6 and 8 take the item this would be represented as
10110011 - reading from right to left, and this would be stored as 179.

I think Lasso will handle a 64 bit value so you can have 64 branches. You'll
need some code to turn the fairly meaningless looking integer values into
branch lists.

If you're using MySQL you can then issue a simple statement like SELECT *
FROM items WHERE branch_code & 16 to see all of the items taken by branch
number 5. Or SELECT * FROM items WHERE branch_code & 19 to see items taken
by branches 1, 2 and 5.

Hope I'm making sense.



on 8/5/06 16:34, Marc Pinnell at [hidden email] wrote:

> I have a current solution that allows branch offices of a single
> corporation to order printing (business cards, envelopes, letterhead,
> etc) and other promo items. Not all branches have access to the same
> set of products. The current solution adds a column to the "items"
> table for each branch (and removes said column if branch is removed).
> Basically just a yes/no column. Is this the "best" way to accomplish
> this or would it be better to have another table (with two columns,
> part number and branch id) that would have an entry for each part
> number and each branch (ie 20 records for part #1111, representing
> the 20 branches that can buy this part)?
>
> Hope this makes sense, I am about to build (copy) another solution of
> similar scope for the same client and wonder if my current solution
> is going to cause trouble down the line.
>
> Thanx for any advice.
> Marc
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:
> http://www.listsearch.com/lassotalk.lasso?manage


Regards


Mark Palmer, Pageworks

T: 01902 620500            F: 01902 620440
E: [hidden email]    W: www.pageworks.co.uk



------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Show of hands, best solution...

JP-14
In reply to this post by Marc Pinnell-3
You could do it 1 of 2 (actually more) ways ...but you asked for  
"best solution"

So, my vote is a tie.

1)  Use JOIN  (although I'm not THAT familiar how one item could have  
branches 1,3,4, and 6 -- and another item could have all the branches  
-- how would that be done in a JOIN as you do need a common field  
between the 2, me thinka).

2)  The way I would do is this:   Set up a table with branches,  
locations, etc.   then you have the item table that holds all the  
info.  A THIRD table with one field from the branches table and one  
from item table -- you could do this with a foreign key relationship  
or use id references or some value to identify the records from the  
item and branches.  When a branch looks for items to order that is  
applicable to that branch ..look in that 3rd table searching for all  
records with that branch id.

jp
On May 8, 2006, at 10:34 AM, Marc Pinnell wrote:

> I have a current solution that allows branch offices of a single  
> corporation to order printing (business cards, envelopes,  
> letterhead, etc) and other promo items. Not all branches have  
> access to the same set of products. The current solution adds a  
> column to the "items" table for each branch (and removes said  
> column if branch is removed). Basically just a yes/no column. Is  
> this the "best" way to accomplish this or would it be better to  
> have another table (with two columns, part number and branch id)  
> that would have an entry for each part number and each branch (ie  
> 20 records for part #1111, representing the 20 branches that can  
> buy this part)?
>
> Hope this makes sense, I am about to build (copy) another solution  
> of similar scope for the same client and wonder if my current  
> solution is going to cause trouble down the line.
>
> Thanx for any advice.
> Marc
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription: http://www.listsearch.com/ 
> lassotalk.lasso?manage

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage