May 20, 2023 at 7:53 pm
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.
May 21, 2023 at 4:02 pm
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]).
May 21, 2023 at 4:54 pm
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".
May 21, 2023 at 10:22 pm
This was removed by the editor as SPAM
May 22, 2023 at 6:36 am
This was removed by the editor as SPAM
May 22, 2023 at 7:01 am
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