I want to build a website for a shop to support e-commerce.
How can you do so??
I suppose that my store database has different types of items (clothes, books, notebooks…etc). Each type has one or more category, each category has one or more subcategory, each subcategory has many items, and each item included in one or more subcategory (and therefore it may be included in many categories but in one type).
Notes:
Any purchasing system (of two levels in its hierarchy) can use this design.
I tended to normalizing the database for better performance while updating and adding the data.
In the ClassifiedItems table (relational table), I didn’t include CategoryID but I included SubCategoryID and therefore I can reach to CategoryID.
In Items table, I included the common fields in all different items. Also I included TypeID (the type id for that item) and ShipmentCost(how much does it cost to ship this item to somewhere for example New York). I’ll describe their use soon.
In Regions table, I included RegionShipmentValue. This field specifies a value that can be multiplied by ShipmentCost field in Items table, and the result will represent the shipment cost for that item for this region. For example, the RegionShipmentValue for Egypt is 3( i.e. triple the value specified in ShipmentCost) and by multiplying them I can have the shipment cost for Egypt for specified item.
why didn’t I specify the shipment cost for each region in the table directly , and I use a mathematical operation to get shipment cost for each region?
That is because each item have a shipment cost (shipping notebook cost you much more money than shipping a book for example. So with each item, I specify its shipment cost.
In Region table, I also included EquivalentValueForDollar field. It stores the region’s equivalent value for dollar. So for example after logging in with my username, the website can show items price directly with my currency.
For more details about some of classified items, I can add some of table to the database. For example, I can add BookDetails (TypeID, SSN, PageNumber, Publisher) and ClothesDetail (TypeID, Size, Color, MadeIn) and make a direct relationship between each new added detailed table and Items table, so that I added TypeID field to Items table to get some details about the item by connecting with the detailed table directly instead of joining 6 tables (Items, ClassifiedItems, SubCategories, Categories, Types, and any detailed table)
This an example of types added and has a relationship between Item table. Here I added bookDetails table and LaptopDetails table that have details about these types.
Amira
[...] topic: Designing DB for a purchasing System. 1st step: ERD I designed the ERD of a purchasing website, I described the tables and their [...]
Pingback by Data Flow Diagram(DFD) for a Purchasing Website. « Notes eMeroo(Some TechTips); — December 22, 2008 @ 4:14 pm
what are syntax or the program for the PO.
THE PROPOSED PURCHASE ORDER SYSTEM
the Purchaser prepares two copies of the Purchase Order basing it on the data from the database. Afterwards, the database is then updated. The Purchaser submits the PO to the General Manager for approval. Before approving the PO, the General manager first looks at he approval PR for further checking. After the PO is approved, one copy is filed while the other is sent to the supplier. The approved PR is also filed.
Comment by ricky — March 17, 2012 @ 7:18 pm