2

I am trying to build a database of recipes. The problem I am facing is that my recipe book also contains recipes for some of my ingredients (a.k.a. intermediary product) Instead of the following structure:

product(
    id VARCHAR(10) PRIMARY KEY NOT_NULL,
    name VARCHAR(100) NOT_NULL,
    description VARCHAR
)
ingredient(
    id VARCHAR(10) PRIMARY KEY NOT_NULL,
    name VARCHAR(100) NOT_NULL,
    description VARCHAR
)
recipe_step(
    step_id INT PRIMARY KEY AUTO_INCREMENT NOT_NULL,
    recipe_id VARCHAR(10) FOREIGN KEY REFERENCES product(id)
    ingre_id VARCHAR(10) FOREIGN KEY REFERENCES ingredient(id)
    amount DECIMAL NOT_NULL
)

I wish to keep product and ingredient in one table. Can I write:

product(
    id VARCHAR(10) PRIMARY KEY NOT_NULL,
    name VARCHAR(100) NOT_NULL,
    description VARCHAR
)
recipe_step(
    step_id INT PRIMARY KEY AUTO_INCREMENT NOT_NULL,
    recipe_id VARCHAR(10) FOREIGN KEY REFERENCES product(id)
    ingre_id VARCHAR(10) FOREIGN KEY REFERENCES product(id)
    amount DECIMAL NOT_NULL
)

In recipe_step everything is measured in grams.

I am a total newbie in RDBMS. I haven't really decided yet, but I wish to start with something simple. If sqlite won't do, I would probably try MySQL.

  • 1
    Could you please clarify what is the real world entity of "product"? – seventyeightist Feb 9 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.