[darek9576]

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!

[Martyr2]

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.

[darek9576]

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 ?

[darek9576]

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.

[macosxnerd101]

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.

[Martyr2]

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.

[JackOfAllTrades]

Merged pretty much duplicate topics.

[darek9576]

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.

[ForcedSterilizationsForAll]

Your best bet is multiple tables. What makes sense to you, as this sounds suspiciously like a homework problem to me?

[macosxnerd101]

Duplicate threads merged again. Please stop opening duplicate threads.

[darek9576]

Of course its an homework problem