1 Replies - 600 Views - Last Post: 18 April 2010 - 10:41 AM Rate Topic: -----

#1 gymangel812  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 116
  • Joined: 15-February 10

Normalization help

Posted 16 April 2010 - 09:33 AM

I'm having a problem with DB normalization. I need to normalize the table below to the 3rd form. The problem for me is where two of rows are exactly the same except for the image name. I'm guessing that for the first normal form I would have 2 primary keys of the name & image name. then I would seperate the table into 2 table with each being the primary key. after that i'm lost...
Column 1:
name
Andouille
Banh Mi
Jidori
Merguez
Pav Bhaji
Pav Vada
Pljeskavica
Poc Chuc
Relleno Torta
Roujiamo
Tartare
Tostee
Tostee
Pan Con Pavo

Column 2:
description
Layered over two lean links, seasoned with no less than three pepper varieties, is a tart-sweet caponata of minced eggplant and sweet peppers sparked with capers, vinegar and plump golden raisins.
Sizzling shards of tender marinated beef are stacked into a French-style torpedo roll so fresh from the shop's ovens it may still be warm. The meat's juices merge with the house-made mayonnaise and the soft crumb of the bread's interior to create a rich, saucy base for the beef and tart-hot pico de gallo garnish.
Miso-marinated Jidori chicken baguette with salty, yeasty miso never dominating the delicate meat, and a subtle tingle of heat from the red radish sprout garnish that brings the sandwich flavors into perfect focus.
Perfumed with cinnamon and redolent of fennel, it comes anointed with a peppery harissa sauce and substantial sandwich buns -- house-baked rolls with a shiny pretzel glaze.
Rich vegetable curry, mounded onto slider-style buns
Tongue-scalding potato-bean patty, laced with fresh herbs on pav rolls and smeared with spicy chutney, puts chile lovers into a reverie.
Baked somun, a descendant of pita, drinks up savory juices from the cevapcici filled with a sausage-like beef and onion patty that's baked inside, with added roasted bell pepper, lettuce, tomato, pickled onion and dollops of a homemade garlic sauce that's potent enough to keep a crowd of vampires at bay.
Served on French baguettes, preferring their firmer texture to sop up the sour-orange-and-garlic-instilled meat juice, with a blend of char and tang (and a splash of fiery habanero salsa if you like).
Atop a grilled roll loaded with avocado slices, a translucent smear of beans and crema, sits a beautifully roasted chile stuffed with melty Oaxaca cheese on a hefty slice of roast pork leg.
A crisp-topped baked bun, dense and layered, with rich carnitas-like roasted pork cubes piled inside topped with tangy pickled leafy greens.
Spicy tuna tartare, rimmed with avocado and wasabi on a raft of sourdough for Sushi lovers.
Pita-like bagel stuffed with feta, olives and house sauce or or mozzarella sluiced with marinara.
Pita-like bagel stuffed with feta, olives and house sauce or or mozzarella sluiced with marinara.
Native wild turkey seasoned with local chiles and served on a European-style torpedo roll.

Column 3:
origin
French, Tunisian
French, Vietnamese
Japanese
French, Tunisian
Indian
Indian
Croatian
French, Mexican
Chilean
Chinese
Japanese
Israeli
Israeli
Salvadoran

Column 4:
image
andouille_sausage.jpg
banh_mi.jpg
miso_marinated_jidori_chicken.jpg
tunisian_merguez.jpg
indian_pav_bhaji.jpg
indian_pav_vada.jpg
croatian_pljeskavica.jpg
yucatan_poc_chuc.jpg
chile_relleno_torta.jpg
chinese_roujiamo.jpg
japanese_tartare.jpg
israeli_tostee.jpg
israeli_tostee_2.jpg
salvadoran_turkey.jpg

Column 5:
price
5.95
4.95
6.95
4.95
5.25
5.25
6.50
5.25
5.50
5.75
6.95
4.95
4.95
5.50

Is This A Good Question/Topic? 0
  • +

Replies To: Normalization help

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4188
  • View blog
  • Posts: 11,852
  • Joined: 18-April 07

Re: Normalization help

Posted 18 April 2010 - 10:41 AM

Your image names will be listed once in its own table and this will be a lookup table. Between the original and the new image table will be a third table called a conjunction table which will be used to describe the relationship. The two fields in this table will form the composite key (aka super key aka compound key).

original_table
-------------
id (primary key)
column1
column2


images
--------
id (primary key)
image_name


original_images
--------
original_key
imageid_key




So with this setup you have each item listed once, each image listed once in its table and a third table which links between the two...

original_table
--------------
1, 'Tostee', 'Pita-like bagel stuffed with feta, olives and house sauce or or mozzarella sluiced with marinara.', 'Israeli', 4.95


images 
----------
1, 'israeli_tostee.jpg'
2, 'israeli_tostee_2.jpg'

original_images
----------
1, 1
1, 2



Notice how the third table has a link back to original's key and to images key and together create a unique key of their own. There will never be two records with 1,1 for instance. Notice how also we no longer have repeats in the original table or the images table. No where in this setup are you going to see a repeat of image names.

Hope you get the idea. This is how you describe a many to many relationship because in the original table we could have had something like "Tostee with creme sauce" and it too links to the israeli_tostee.jpg image. Update that image and it updates the image for both items. The image is no longer dependent on the original name.

:)

This post has been edited by Martyr2: 18 April 2010 - 10:41 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1