Thread: Data model question regarding usage of arrays.

Hi,

I'm looking at a very simple database that contains a table
of recipes and a table of ingredients with nutritional content.

I'd defined the schema as such

CREATE TABLE ingredients
(
    uid integer,
    ...     -- nutritional info
)

CREATE TABLE recipes
(
   uid integer,
   ingredients integer[] -- uids in table ingredients
)

however I am reading that overall the usage of arrays in
data schema is frowned upon.

This seemed the lowest level @ which I could do things.

Is there a higher level SQL concept that would encapsulate
the "many to many" mapping of rows in the recipes table to
rows in the ingredients table?

Thanks,
- Eric

Solution

CREATE TABLE ingredients
(
    uid integer,
    ...     -- nutritional info
)

CREATE TABLE recipes
(
   uid integer,
   uid_bridge integer -- uids in bridge table
)

CREATE TABLE bridge
(
   uid integer,
   uid_ingredients integer -- uids in table ingredients
)

rows in recipes table can be unique for each recipe, rows in
bridge table are a row per recipe per ingredient in that recipe.

Wait, nm,

no need for uid_bridge in recipes

On Mon, Mar 27, 2006 at 05:08:58PM -0800, Ricebot wrote:
> Wait, nm,
>
> no need for uid_bridge in recipes

Sure there is.  If one recipe can contain several ingredients and an
ingredient can be in more than one recipe, you need that join table :)

Cheers,
D (who isn't going to try to design a cookbook db right now)
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!