why do you think you may have to separate the calorific value from the foodstuff itself?
If I were you I'd store calorific value as part of the food product, as its an element that describes the foodstuff. putting it in a separate table makes no sense in the relational db model.
Personally Id make the value nullable and the value = to anything greater than or equal to 0.
However I think you may be getting yourself confused with the defintiion of Groceries. The way you have modeled it at present you will have to have a differeent grocery definition for each an every company, and shop. I suspect in the real world a company may have one or more shops, each shop may sell groceries.
buyin a carrot from shop A from company 1 should be the same as a carrot from shop b from the same company. Indeed many would argue a carrot is a carrot is a carrot, irrespective of which shop it came from, irrespective of which company it came from. Arguably the price may different in each shop, but the calorific value should remain the same for a given value of carrot. So I think you need to look again there.
The category id looks fine, unless say you wante to subtype categories
ParentID, integer, nullabel FK to Category.ID
why would i need a different definition for each grocery?
because you are defining your grocery table as
GROCERIES(pk_id, name, price, calorie, fk_company_id*, fk_shop_id*, fk_category_id*)
your primary key is PK_ID that makes each grocery record unique
you then have foreign keys of companyID and shopID
so that would mean either you can only ever have one grocery from one company AND on shop or if you incorporate those elements into the PK as a composite key you will have to have a different defintion for each company AND shop.
Originally Posted by demiurgen
is there a solution to this that i might understand as a newbie or have i taken on too much?
only you can say, but I don't think you have taken on too much.. understanding the fundamentals such as normalisation is a requirement in relational db design
the "homework assignment" is:
i want to make a database of all the stuff that i buy every week, like carrots soap, milk etc.
so that i later can create a kind of webshop solution where i can select different items to create a shopping-list or a list of all the stuff i have eaten in a day or a recipe or maybe even a budget... (in about 10 years)
this may be an impossible assignment or more likely - i am too stupid!
I find it easier to visualise the model rather than as a line of text, so....
Heres a quick and dirty representation of my perception of what your are thinking about, it isn't finished or polished its a starting point (with some errors built in I suspect)
Another technique is to describe each table in a simple line of text. eg..
A company has many shops
A Shop has many groceries, each shop has different prices for groceries
A Product category may have a (or no) parent category, and (optioanlly) may also have multiple child categories. A product category may identify one or more (optionally none) groceries
A Grocery item has a product category, and Price(which is unique to a shop).
The ShopGroceries is called an intersection table, amongst other things. its a way of associating two separate entities that have an overlap. This way round you can have multiple shops defining the same grocery item, with their own price, and multiple grocery items within a shop defining their own price
There is no one right (or wrong) way of creating the data model... what matters is does it work for you.
One of the mantras of many data modelling people is "the key and only they key". and think of the key as the elements that make that specific table element unique.
in my example I took Company and Shop to mean for example Tesco has multiple shops / outlets, not suppliers.
if you wanted a supplier relationship then that would change the model.
So I'd suggest you rejig it..... the best way to learn is to have a crack at it.
think about it
a shop may have multiple suppliers
a supplier may supply one or more items
one of the problems is if there are products you want to rack by manufacturer/supplier. Quite often you will come across generic items, where the actual supplier is irrelevant or unknown (groceries are often an example of this).
beforte you commit to that sit back, have a think
does that model support what you want to achieve
you mentioned you wanted to use this to learn about application development, by using this track your grocery shopping.
is company modelled correctly
you mentioned that youi were uncertain if it referred to supplie reof shop owner.
If you only visit one shop form anyone company then arguably you don't need a table company
if you need to track a specific supplier then you need some method of associating a product with a supplier.
To a certain extent its down to you and how you want to model the requirement. Ultimately that comes form the user requirement.. and in this its vague and imprecise, because its effectively an academic exercise.
the model at present doesn't allow for specifying the supplier / manufacturer of the goods. is that a problem.. I don't know, ultimately thats down to you. for example you could argue that the manufacturer is irrelevant, you'll put it in the description. you could decide its critical in which case you need some mechanism to associate the manufacturer with a product.
if you do decide to go down that route.. how would you do it. how would you identifyt waht manufacturer(s) produced what products