Database Design Help

  • Hello, I am looking for some input on a database I am attempting to design for a video game.

    I have most of the design basically done, but I am having trouble finishing this last part.

    The basis of the database is around the entities "item", "fluid", "node", and "building". The schema related to these entities are finished.

    The last part that I am having trouble with is the entity "recipe". This "recipe" entity will be used to house (obviously) a recipe. A recipe gets produced in a building(s), has input(s) of "item" and/or "fluid" of varying amounts, and outputs an "item" and/or "fluid" of varying amounts. Im not sure of the best way to implement this as each recipe associated with a building can have 2 item inputs, 1 fluid input, 1 item output and 2 fluid outputs, or any combination thereof.

    This is the best way I can describe it, if you need more information let me know. Any help/input is greatly appreciated!

    Thank you.

  • a generic recipe is something like this:

    CREATE TABLE Recipe (

    FinalProductID INT NOT NULL,

    IngredientID INT NOT NULL,

    Quantity INT NOT NULL CHECK (Quantity>0)

    CONSTRAINT pkRecipe PRIMARY KEY (FinalProductID, IngredientID)

    );

    The "amount" output is the SUM(Recipe[Quantity]).

  • I suggest you consider "ingredients" as an entity rather than separate "items" and "fluids". ingredient would be a super-type, with sub-types of either item or fluid, while allowing for other sub-types in the future (similar to the way that in SQL Server sys.objects is a super-type, and "user table", "procedure", "function", etc., are the sub-types).

    Note that since this is still a logical design rather than a physical one, "integer" just means "non-decimal", that is, the physical implementation could end up being tinyint, smallint or int.

    ingredients

    ingredient_number integer primary_key

    description character --"ground beef", "butter", "vinegar", etc.

    type character ('Fluid', 'Item')

    recipes

    recipe_number integer primary_key

    building_number integer required --references building entity

    date_created date optional

    date_available date required

    recipe_inputs

    recipe_number primary_key_1 --references recipes

    input_number integer primary_key_2

    ingredient_number integer required -- references ingredients

    ingredient_quantity decimal required

    ingredient_measurement character "ounces", "cup", "ml", etc.

    instructions character optional --e.g., "stir in", "saute", etc.

    recipe_outputs

    recipe_number primary_key_1

    output_number integer primary_key_2

    ...

     

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply