Database Design – SQL Database for Tire Purchase System

database-design

Ok so I don't have code for this as I am unsure of how it would even go. Take this scenario I am working on, a purchase system for, let's say, tires.

I have one table named tStock and another one for customers named as cOrder.

tStock has these columns:

(PK) Tyre_ID
(VARCHAR) Manufacturer
(int) Quantity

while cOrder has:

(PK) Customer_ID
(varchar) Cust_name
(int) Purchase_quantity

What I hope to achieve is that when I have a form that takes a customer's details and orders of how many tires (s)he wants min of 1 tire max of 4 tires that it will then subtract this amount from the database and update it.

Would I be right in thinking that I would need to have customer_ID and possibly purchase_quantity in as a foreign key constraint for it to update?

Also would it need to be joined in someway i.e outer, equijoin, natural?

Any suggestions is greatly appreciated

Best Answer

I would have three tables minimum for a purchasing system. One for customer, one for products, one for orders.

    --Customer
CREATE TABLE [dbo].[Customer](
    [CustomerId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [AdditionalInfoRequired] [varchar](50) NOT NULL,
 CONSTRAINT [CustomerId] PRIMARY KEY CLUSTERED 
(
    [CustomerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
--Product
CREATE TABLE [dbo].[Product](
    [ProductId] [int] IDENTITY(1,1) NOT NULL,
    [ProductName] [varchar](50) NOT NULL,
    [Quantity] [int] NOT NULL,
 CONSTRAINT [ProductId] PRIMARY KEY CLUSTERED 
(
    [ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
--Order
CREATE TABLE [dbo].[Order](
    [OrderId] [int] IDENTITY(1,1) NOT NULL,
    [CustomerId] [int] NOT NULL
 CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
(
    [OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order]  WITH CHECK ADD FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([CustomerId])
GO
--OrderDetails
CREATE TABLE [dbo].[OrderDetails](
    [OrderDetailsId] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NOT NULL,
    [Quantity] [int] NOT NULL,
 CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED 
(
    [OrderDetailsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderDetails]  WITH CHECK ADD  CONSTRAINT [FK_OrderDetails_Product] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Product] ([ProductId])
GO
ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_Product]
GO

Let me know if you have any question.

Related Question