Re: Properly Normalizing a Products Database to work harmoniously with Cake

table widget (
`id` primary key
`number` unique widget number
... common fields to all widgets
)

table properties (
`id` primary key
`widget_id` foreign key
`property` name of property
`value` value of property
`type` type of value (int, string, float, enum, etc...)
)

table categories (
`id` primary key
`name` category name
)

table `categories_widgets` (
`id` primary key
`category_id` foreign key
`widget_id` foreign key
)

widget:
hasMany properties
habtm categories

category:
habtm widgets

property:
belongsTo widget

After a number of similar projects with similar relationships, I would
say this is the best solution.
It can be extended as needs be with little worry for all your "new"
property needs.

On Nov 24, 7:00 pm, plasmaflux <magneticmonk...@gmail.com> wrote:
> Hello all!
>
> I'm a novice to CakePHP, have a rusty set of PHP skills, and am maybe
> a 5/10 when it comes to MySQL & databases in general. I was recently
> hired by a company wanting to bring their catalog design and web
> development in-house. After digging through the existing site's raw
> PHP code, I've decided to rebuild it from scratch with Cake. I'm blown
> away by what Cake can do and how it does it! My last large-scale PHP/
> MySQL project was during the php3-php4 transition when PHPLib was the
> hottest thing around.
>
> My company sells Widgets in the Medical field...lots of them...nearly
> 20,000. My largest task at hand is to build an app to both populate a
> Widgets database and search the data. It's a little overwhelming and I
> need some advice.
>
> First, all Widgets have a unique Widget Number. All Widgets can be
> divided into Top-Tier categories a couple different ways. For example,
> a Widget could fall under Forceps or Scissors or Clamps, and could
> also land under Cardiovascular and/or Neurospinal and/or Dental. A
> Clamp will always be just a Clamp, but the same Clamp could be useful
> in both Cardiovascular AND Neurospinal. So categorization presents a
> challenge.
>
> Additionally, all Widgets have a number of properties in common. They
> all have a Price. They all have a Name. They all have Dimensions.
> Here's the really tricky part that's making my brain hurt. They also
> all have a number of properties that are unique either to the item OR
> to the Widget's category. A Clamp may have a curvature with a specific
> radius. A set of Forceps may have an angle. A Needle Holder may be
> Straight or Curved, and may also have jaws that are either Stainless
> Steel, Tungsten Carbide Coated, or Titanium with Tungsten Carbide
> coating.
>
> There are also relationships between certain items that I'd want to be
> available for "Related Widgets" display on a Widget's page. For
> example, a Titanium Needle Holder with Tungsten Carbide Coating may be
> available in 6", 7", 8", 9" lengths, with jaws that are 1mm, 2mm, 3mm,
> 4mm wide and jaws that are Straight or Curved. I need to somehow be
> able to make it clear in the database structure which Widgets are
> related to other Widgets and how; keeping in mind refinable searches
> (Show me all Titanium Curved-jaw Needle Holders).
>
> Then there are things like Cases that hold predefined Sets of Widgets,
> Widgets with X and Y dimensions vs. Widgets with X, Y and Z
> dimensions.....and on and on and on.
>
> Not only am I trying to figure out how to create this beast of a
> relational database, I'm also trying to ensure from the beginning that
> it's optimized for CakePHP and remains highly flexible for all the
> unforeseen additions and changes down the road.
>
> Brain.....very.....exploding. Please help!
>
> A thousand thank-yous in advance to all who read and any who respond!
>
> ~PF

Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions.

You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en