🗂️ Database Design Of AmarTech

AmarTech utilizes a relational database design with Entity Framework Core to manage entities and enforce relationships. Below is an overview of the core data models used in the system:

🧑 ApplicationUser (inherits from IdentityUser)

• Stores user profile details including name, address, and associated company.
• Linked to Company via CompanyId.
• Supports role tracking via Role (non-persistent).

🏢 Company

• Holds company-specific information such as address, city, state, and phone.
• Used for associating users with business accounts.

📦 Product

• Represents items for sale.
• Linked to Category through CategoryId.
• Stores stock, discount, and audit metadata (created/updated).

🗃️ Category

• Classifies products.
• Includes display order and audit metadata.

🛒 ShoppingCart

• Temporary cart for each ApplicationUser.
• Tracks selected product and quantity before checkout.

📑 OrderHeader

• Stores high-level order data.
• Includes user info, payment status, shipping, and billing address.

📦 OrderDetail

• Line-item detail for each order.
• Links a specific product to an order with count and price.

ER Diagram Of AmarTech

🔗 Database Relationships Overview

1. ApplicationUser ↔ Company

• Type: Optional One-to-One
• Foreign Key: ApplicationUser.CompanyId → Company.Id
• Details: A user may or may not belong to a company.

---

2. ApplicationUser ↔ OrderHeader

• Type: One-to-Many
• Foreign Key: OrderHeader.ApplicationUserId → ApplicationUser.Id
• Details: One user can place multiple orders.

---

3. ApplicationUser ↔ ShoppingCart

• Type: One-to-Many
• Foreign Key: ShoppingCart.ApplicationUserId → ApplicationUser.Id
• Details: A user can have multiple items in their cart.

---

4. Category ↔ Product

• Type: One-to-Many
• Foreign Key: Product.CategoryId → Category.Id
• Details: Each product belongs to one category.

---

5. Product ↔ ShoppingCart

• Type: One-to-Many
• Foreign Key: ShoppingCart.ProductId → Product.Id
• Details: A product can exist in many users' carts.

---

6. OrderHeader ↔ OrderDetail

• Type: One-to-Many
• Foreign Key: OrderDetail.OrderHeaderId → OrderHeader.Id
• Details: Each order can contain multiple order details (products).

---

7. Product ↔ OrderDetail

• Type: One-to-Many
• Foreign Key: OrderDetail.ProductId → Product.Id
• Details: A product can appear in multiple orders.

📦 2️⃣ Database Schema

🔐 ApplicationUser Table

Column Name	Data Type	Description
Id	VARCHAR(450)	Primary Key, unique identifier for each user.
Name	VARCHAR	Full name of the user.
StreetAddress	VARCHAR	Optional street address.
City	VARCHAR	Optional city name.
State	VARCHAR	Optional state name.
PostalCode	VARCHAR	Optional postal/ZIP code.
CompanyId	INT	Foreign key linking to Company.
Role	VARCHAR	Not mapped. Used internally to store user role.

🏢 Company Table

Column Name	Data Type	Description
Id	INT	Primary Key.
Name	VARCHAR	Name of the company.
StreetAddress	VARCHAR	Company's street address.
City	VARCHAR	City location of the company.
State	VARCHAR	State location of the company.
PostalCode	VARCHAR	Postal code of the company.
PhoneNumber	VARCHAR	Contact phone number (BD format).

🗂️ Category Table

Column Name	Data Type	Description
Id	INT	Primary Key.
Name	VARCHAR(30)	Name of the category.
DisplayOrder	INT	Order in which category is displayed.
IsActive	BOOLEAN	Whether category is active.
CreatedBy	VARCHAR	User who created the category.
CreatedDate	DATETIME	Date of creation.
UpdatedBy	VARCHAR	User who last updated.
UpdatedDate	DATETIME	Date of last update.

📦 Product Table

Column Name	Data Type	Description
Id	INT	Primary Key.
Title	VARCHAR	Product title.
Description	VARCHAR(1000)	Description of the product.
ImageUrl	VARCHAR	Image URL.
Price	DECIMAL	Price of the product.
CategoryId	INT	Foreign key linking to Category.
StockQuantity	INT	Available quantity.
DiscountAmount	DECIMAL	Discount on product.
IsActive	BOOLEAN	Whether product is available.
CreatedBy	VARCHAR	Created by user.
CreatedDate	DATETIME	Date created.
UpdatedBy	VARCHAR	Updated by user.
UpdatedDate	DATETIME	Date updated.

🧾 OrderHeader Table

Column Name	Data Type	Description
Id	INT	Primary Key.
ApplicationUserId	VARCHAR(450)	Foreign key to ApplicationUser.
OrderDate	DATETIME	Date of order.
ShippingDate	DATETIME	Date of shipping.
OrderTotal	DOUBLE	Total amount.
OrderStatus	VARCHAR	Current order status.
PaymentStatus	VARCHAR	Payment status.
TrackingNumber	VARCHAR	Courier tracking number.
Carrier	VARCHAR	Shipping carrier.
PaymentDate	DATETIME	Date of payment.
PaymentDueDate	DATE	Due date for payment.
SessionId	VARCHAR	Stripe session ID.
PaymentIntentId	VARCHAR	Stripe payment intent.
PhoneNumber	VARCHAR	Shipping contact number.
StreetAddress	VARCHAR	Shipping address.
City	VARCHAR	City for shipping.
State	VARCHAR	State for shipping.
PostalCode	VARCHAR	Postal code.
Name	VARCHAR	Recipient's name.

🧾 OrderDetail Table

Column Name	Data Type	Description
Id	INT	Primary Key.
OrderHeaderId	INT	Foreign key to OrderHeader.
ProductId	INT	Foreign key to Product.
Count	INT	Number of units ordered.
Price	DOUBLE	Price per unit at the time of order.

🛒 ShoppingCart Table

Column Name	Data Type	Description
Id	INT	Primary Key.
ProductId	INT	Foreign key to Product.
Count	INT	Quantity in cart.
ApplicationUserId	VARCHAR(450)	Foreign key to ApplicationUser.
Price	DOUBLE	Computed (not mapped) price.

Thank you for reading our Database design wiki page, Happy Coding 🤗