Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Apr 2006
    Location
    Norway
    Posts
    26

    need help to normalize

    i have a database that looks like this:

    GROCERIES(pk_id, name, price, calorie, fk_company_id*, fk_shop_id*, fk_category_id*)
    COMPANY(pk_id, name)
    SHOP(pk_id, name)
    CATEGORY(pk_id, name)

    should i separate the calorie column from the groceries table (not all groceries have calories) or accept 0 values?
    how can i do this?

    i don't see the point of having a table like this:
    CALORIES(pk_id, amount)
    and then link that to a FK in the groceries table.
    i might be wrong or there might be a better way. can someone help me?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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
    eg
    Category
    ID integer,pk
    Desc string/varchar
    ParentID, integer, nullabel FK to Category.ID

    10 | vegetables | null
    20 | Green Veg | 10
    9 | root Vegetables | 10
    98 | Potatoes | 9
    80 | King Edward | 98
    80 | Desiree | 98
    99 | Carrots | 9

    that way round you could model a hierarchy that would allow the system to group together similar products. if you are uncertain of normalisationm thaere are lots of good starting points out there, including a page by Paul Litwin, reprinted on Rudy (R937's) site.. well worth a read

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by demiurgen
    i don't see the point of having a table like this:
    CALORIES(pk_id, amount)
    Hallelujah

  4. #4
    Join Date
    Apr 2006
    Location
    Norway
    Posts
    26
    i don't think i understand you.

    why would i need a different definition for each grocery?

    is there a solution to this that i might understand as a newbie or have i taken on too much?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by demiurgen
    i don't think i understand you.

    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.

    Quote 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

  6. #6
    Join Date
    Apr 2006
    Location
    Norway
    Posts
    26

    ok. then i understand.
    but sadly i don't see a solution.

    do you?

    but is it a poor db if it looks like this:
    carrot1 shop2 company4 price1
    carrot2 shop1 company2 price3
    carrot1 shop1 company4 price2

    aren't the carrot unique even though they are the same brand if they have different prices in different shops.
    Last edited by demiurgen; 01-06-08 at 15:33.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    What type of relationship do you think is needed here?
    a) one to one
    b) one to many
    c) many to many
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    come on guys, help the brother out, a ternary relationship is not the easiest thing to grasp as a rookie

    unless each shop belongs to only one company

    but the last shop i was in, they had products from many companies, but not all shops carry stuff from all companies...

    or maybe the product comes from only one company...

    nope, carrots in the same shop can come from multiple companies...

    you know what's missing here?

    we haven't seen the actual wording of the homework assignment

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2006
    Location
    Norway
    Posts
    26
    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!

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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.
    Attached Thumbnails Attached Thumbnails grocerymodel.png  

  11. #11
    Join Date
    Apr 2006
    Location
    Norway
    Posts
    26
    AWESOME!! thanks healdem.

    i'll try and see if can understand this.

    i just noticed something though, a little misunderstanding.
    it is poor labeling by me but with company a actually mean manufacturer or producer like for instance cadburys, nestle etc.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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).

  13. #13
    Join Date
    Apr 2006
    Location
    Norway
    Posts
    26
    all right!
    i think i understand some of it now, so i am gonna go ahead and create it.
    this is how it looks to me:

    COMPANY(id, name)
    SHOP(id, company_id*, address)
    SHOPGROCERIES(shop_id*, groceries_id*, price)
    GROCERIES(id, prodcategory_id*, desc_2, calorificvalue)
    PRODCATEGORY(id, prodcategory_id*, description, parent_id)

    CREATE TABLE `Company` (
    `id` int(11) NOT NULL auto_increment,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
    )

    CREATE TABLE `Shop` (
    `id` int(11) NOT NULL auto_increment,
    `address` varchar(255) NOT NULL,
    `company_id` int(11) NOT NULL,
    PRIMARY KEY (`id`)
    )

    CREATE TABLE `ShopGroceries` (
    `shop_id` int(11) NOT NULL auto_increment,
    `groceries_id` int(11) NOT NULL,
    `price` real NOT NULL,
    PRIMARY KEY (`shop_id `, `groceries_id `)
    )

    CREATE TABLE `Groceries` (
    `id` int(11) NOT NULL auto_increment,
    `prod_category_id` int(11) NOT NULL,
    `desc_2` int(11) NOT NULL,
    `calorific_value` float NOT NULL,
    PRIMARY KEY (`id`)
    )

    CREATE TABLE `ProdCategory` (
    `id` int(11) NOT NULL auto_increment,
    `description` varchar(255) NOT NULL,
    `parent_id` int(11) NOT NULL,
    PRIMARY KEY (`id`)
    )

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    How come none of your fields are nullable?
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •