sql database design for multiple restaurants -

sql database design for multiple restaurants -

i'm storing orders multiple restaurants.(not chain) these restaurants have unique items may or may not in same category(sweet, salty, drinks, entrees, appetizers, etc) can have sub categories well(sweet->parfait, crepe; drinks->hot, cold, alcohol, etc)

for each order, there may more 1 item, customizations(removals or additions), , special notes not part of customization. serializing info json string easy option, hard search through , organize valuable data.

each item comprised of parts may or may not mutual other items in category. item has parts every possible combination. default parts opt-out-able.

let's start example. category, subcategory, mutual options, item name, unique item options. not levels used always.

restaurant a

sweet ice cream # scoops (if > 1, multiple flavors possible) vanilla chocolate mango frozen yogurt variation 1 a (default) b (default) c d e variation 2 a b (default) c d (default) e variation 3 a (default) b (default) c d e (default) savory pizza hawaiian pineapple (default) cheese (default) ham (default) tomato sauce (default) bacon mushrooms pepperoni create own pineapple cheese ham tomato sauce bacon mushrooms pepperoni drinks hot coffee latte cappucino tea cold coke bottle water ice coffee/tea variations

restaurant b

sweet item x item y item z savory item w item q item r

restaurant c

item o item p item g item s

if go restaurant a, , order double scoop of vanilla , chocolate, 2 frozen yogurt variation 1, 1 frozen yogurt variation 2 no b, , hawaiian pizza mushrooms, how store order? or using sql wrong thought , should utilize document store (nosql) system? ideally don't want store (default) parts part of customizations; things different interesting.

learn info modeling , relational model.

a straightforward info model application best start. (actually, programming problem.) , should utilize relational dbms.

you need larn info modeling (im). relational model (rm) , (sort-of-)relational database management systems (rdbmss). im gets precise uses rm. see this answer. first link. see answers modeling questions.

the rm allows generic loop-free/declarative (logic notation) querying , manipulation automatic implementation performance optimizations. other info construction or language should used because of engineering science tradeoffs particular queries , performance. every computing task. (the precise specification of programme can written in logic notation.)

you can stop now. rm way of thinking follows.

find application relationship statements.

the basic thought find statements describe application situation.

i go restaurant a, , order double scoop of vanilla , chocolate, 2 frozen yogurt variation 1, 1 frozen yogurt variation 2 no b, , hawaiian pizza mushrooms

make fill-in-the-blanks versions.

person [p] @ restaurant [r] has pending order o , o includes item item , item water ice cream , item has [n] scoops of flavour [f] ... , order o includes item j , item j pizza , item j base of operations hawaiian , item j has [mushrooms]

find basic statements.

person [p] @ restaurant [r] has pending order [o] order [o] includes item item item [i] water ice cream item has [n] scoops of flavour [f] ... item [j] pizza item [i] has base of operations [b] base of operations [b] has topping [t] item [i] has topping [t] item [i] has topping [t]

a base of operations table has statement.

each basic statement gets base of operations table. base of operations table declaration looks shorthand statement & vice versa.

order(p,r,o) -- person [p] @ restaurant [r] has pending order [o] base(b,t) -- base of operations [b] has topping [t] topping(i,t) -- item [i] has topping [t]

a base of operations table holds rows create true. eg in next situation

person no_name @ restaurant placed order 12345 , person philipxy @ restaurant placed order 22222 , person philipxy @ restaurant b placed order 33333 , other (p,r,o) not order(p,r,o)

the base of operations table

order(p,r,o) -- person [p] @ restaurant [r] has pending order [o]

has value table

+----------------------+ | p | r | o | +======================+ | no_name | | 12345 | +----------------------+ | philipxy | | 22222 | +----------------------+ | philipxy | b | 33333 | +----------------------+

the rm way find best basic statements , tables normalization (to 5nf).

every table has statement

every statement made of other statements has table. every table holds rows create statement true. statement combined logic operators , table combined/calculated corresponding table operators.

eg rows where

there's restaurant r, order o , item where: person [p] @ restaurant [r] has pending order [o] , order [o] includes item [i] , item [i] has [n] scoops of flavour [f] , [p]=no_name , [r]=a

or using shorthands rows where

exists r,o,i: order(p,r,o) , item(o,i) , cone(i,n,f) , p=no_name , r=a

ie rows in

select p,n,f order bring together item bring together cone order.o=item.o , item.i=cone.i , order.p=no_name , r=a +--------------------------+ | p | n | f | +==========================+ | no_name | 1 | vanilla | +--------------------------+ | no_name | 1 | chocolate | +--------------------------+

start statements. ie application relationships. ie relations. hence relational model.

alas.

probably modeling advice talk "an" "m:n" "association" [application relationship] without asking which one. ie which statement. (beware utilize "relationship" in non-everyday non-rm way.)

entity-relationship modeling (erm) , object-relational mapping/modeling (orm) misunderstand rm. muddle above. can convert above notions.

sql database-design