10 Replies - 2880 Views - Last Post: 20 January 2011 - 11:02 AM Rate Topic: -----

#1 darek9576  Icon User is offline

  • D.I.C Lover

Reputation: 198
  • View blog
  • Posts: 1,672
  • Joined: 13-March 10

Drawing an ER diagram.

Posted 19 January 2011 - 04:13 PM

Hi. Im quite new to databases and i have a question about ER diagram I have a simple scenario:

"Comapany is selling products. Some of them may be on special offer. An offer works in 2 ways: either the percentage discount or the free gift offer (free goft can be another prodcut). Only ONE type of offer can be added to a product and the offer is onl available for a period of time".

So here goes my ER diagram in plain english : ( :

I would have a table called products_table with 2 columns:

id - primary key
name of product


Then i would have an offers_table that would hold 2 columns:

products id - that would specify which product is on offer
type_of_offer - specify whether its a discount or other prodcut free offer.


Then i would have 2 tables for 2 special offer deals: percentage_discount_table AND free_gifr_table where the first would have:

id of the product
amount discounted


Second would have:

id of product
another id of prodcut that would be free for that product



But then i have NO idea how to include the time constraint period.

So my question is: is this table relations correct? or sohuld i modify it and how to incude time constraint.
Thanks for feedback!

Is This A Good Question/Topic? 1
  • +

Replies To: Drawing an ER diagram.

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4189
  • View blog
  • Posts: 11,863
  • Joined: 18-April 07

Re: Drawing an ER diagram.

Posted 19 January 2011 - 04:36 PM

How about you create your table listing nothing but an ID and a discount amount. This is your discount table. Create the other table with the ID and the special offer product ID. Then the piece you are missing is the conjunction table which will consist of...

id (primary key)
product_id (foreign key)
special_offer_key
special_offer_type
start_date
end_date



You see, the start date and end date applies to the relationship, it doesn't apply to the products or special offers themselves. Since it applies to the relationship, you will find such information as fields in a conjunction table (a table which joins to other tables by their keys).

Here would be an example row from this table...

123, PRODID1, SPEC3, D, 2011-01-01, 2011-02-01



The above would say that row number 123 of this table links a special promotional item "SPEC3" from the "D"iscount table to product "PRODID1" from Jan 1, 2011 to Feb 1, 2011.

This is one way you could do it. But the trick here is that the date information is describing the relationship. How long the relationship between a product and its promotional item is good for.

I hope you understand. :)
Was This Post Helpful? 1
  • +
  • -

#3 darek9576  Icon User is offline

  • D.I.C Lover

Reputation: 198
  • View blog
  • Posts: 1,672
  • Joined: 13-March 10

Re: Drawing an ER diagram.

Posted 19 January 2011 - 04:41 PM

So the example you have provided is the "D"iscount table. And if it was the other offer - the other product. Would that field just point to other table holding the id and other product ?
Was This Post Helpful? 0
  • +
  • -

#4 darek9576  Icon User is offline

  • D.I.C Lover

Reputation: 198
  • View blog
  • Posts: 1,672
  • Joined: 13-March 10

Re: Drawing an ER diagram.

Posted 19 January 2011 - 06:31 PM

Hi. I have a simple question regarding database. Lets say a company sells products in 10 different shops and it wants to keep the track of how much of each product each shop sold.

Would then the table be constucted as follows?:

Option 1:

One table called products_table that has attributes for each shop and an attribute for product id recording how much each shop sold of the given product

OR

Option2:

One table holding products names and its id AND Another table that holds all 10 shops and product_id from first table.

OR maybe you guys would use something else?

Thanks for help.
Was This Post Helpful? 0
  • +
  • -

#5 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10186
  • View blog
  • Posts: 37,613
  • Joined: 27-December 08

Re: Drawing an ER diagram.

Posted 19 January 2011 - 10:10 PM

How about a table:
product_id | store_id | sale_quantity



This would honestly be more like a view, because sale_quantity is aggregate data, and you would have to query for it from the sales table.
Was This Post Helpful? 0
  • +
  • -

#6 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4189
  • View blog
  • Posts: 11,863
  • Joined: 18-April 07

Re: Drawing an ER diagram.

Posted 20 January 2011 - 12:41 AM

The example I provided is a third table separate from a discount or special item table. The "D" is saying that this particular special so happens to be pointing to a record in the discount table. I could make it "S" and reference an ID in the special product table.
Was This Post Helpful? 0
  • +
  • -

#7 JackOfAllTrades  Icon User is online

  • Saucy!
  • member icon

Reputation: 5959
  • View blog
  • Posts: 23,229
  • Joined: 23-August 08

Re: Drawing an ER diagram.

Posted 20 January 2011 - 05:11 AM

Merged pretty much duplicate topics.
Was This Post Helpful? 0
  • +
  • -

#8 darek9576  Icon User is offline

  • D.I.C Lover

Reputation: 198
  • View blog
  • Posts: 1,672
  • Joined: 13-March 10

Re: Drawing an ER diagram.

Posted 20 January 2011 - 05:21 AM

Hi. I have a question. I have the following scenario. The shop sells products and the products are grouped by their cateories as well as product brands.

How would you draw an ER diagram or it?

would you have a Product_Table an in it:

 product_id | category | brand    


??Can anyone hel? Or do i need multiple tables for this simple scenario.
Was This Post Helpful? 0
  • +
  • -

#9 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: Drawing an ER diagram.

Posted 20 January 2011 - 07:07 AM

Your best bet is multiple tables. What makes sense to you, as this sounds suspiciously like a homework problem to me?
Was This Post Helpful? 0
  • +
  • -

#10 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10186
  • View blog
  • Posts: 37,613
  • Joined: 27-December 08

Re: Drawing an ER diagram.

Posted 20 January 2011 - 07:15 AM

Duplicate threads merged again. Please stop opening duplicate threads.
Was This Post Helpful? 0
  • +
  • -

#11 darek9576  Icon User is offline

  • D.I.C Lover

Reputation: 198
  • View blog
  • Posts: 1,672
  • Joined: 13-March 10

Re: Drawing an ER diagram.

Posted 20 January 2011 - 11:02 AM

Of course its an homework problem
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1