0

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

0

1 Answer 1

2

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.

3
  • Notice the foreign key constraints on the Order table. It insures data integrity and it's also pretty much the norm. I had to edit it for it made no sense to have an identity column on orderId the way I originally proposed it. I've added OrderDetails. So, you would have one entry for each order, and another table with its details.
    – WickedFan
    Commented Mar 18, 2015 at 15:47
  • by using this method would this eliminate the need for an inner join between the tables?
    – Topher
    Commented Mar 18, 2015 at 16:21
  • To insure data integrity, you should have foreign keys as in the edited code above. It just makes sense to have one entry for each order (receipt #) and a table with its details. You should also add other relevant columns such as ReceiptNumber as a surrogate key in the Order table. If you don't have any foreign key, the program and database maintenance will be a nightmare down the line.
    – WickedFan
    Commented Mar 18, 2015 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.