I am thinking of doing a fake PC company site for my ASP project. sowhat they will have is a chat, products with reviews, and users canhave "Buddies".
So my DB so far (Tables):
ProductPC - ID, Processor, RAM, HDD, Graphics, LCD ...ProductHardware- ID, Title (Like "Intel C2D E6600"), Description, Price, Rating (0-5stars, so integer), Category (CPU, HDD, Graphics)ProductSoftware - ID, Title (eg. "Adobe CS3"), Description, Price, Rating, Category (eg. Design, Programming)Reviews - ID, ProductID, Title, Content, DateChat - ID, TopicID, Title, Content, DateUsers - ID, Username, Password,The problem, how do i connect Reviews to the products since they are from diff tables.
How do i get the "buddy" system working
Chat i think its not as simple as that
But i just need a simple ASP project, so no need to get too complex, but i still hope to learn as much.
Concerning
you have a problem since Review.ProductId can refere to 1 of 3 tables. To overcome this, I suggest you create a ProductTable thus:
Product - Id, Cost, Title, IsPC, IsHardware, IsSoftware
Change the Id column on ProductPc, ProductHardware and ProductSoftware tables to be non-identity. When a Product entry is created, one and only of the Is must be true. The appropriate record of ProductPc, ProductHardware or ProductSoftware can be created using the the New Product.Id and the Id value. This will allow your review table to work.|||
sorry... but what do u mean? taking off my IDs? then when i add to DB how should i add?
Thanks. i am getting you. but the problem id how do i acheive the part of having the ID to be unique, do i need to code that? because now i have set those IDs to identity and auto increment. if they are not identity, then how do they be unique (automatically)? and to have product ID to be unique across 3 tables?
The sequence for creating a new record is:
Create the new product reference
|||mmm... Scope_Identity() is something unfamiliar to me right now i will search abt it later. for now i am going off to school.
|||is it ok if i have 1 Reviews Table with the ProductID linking to 3 Tables? PC, Hardware & Software Table
So,
PK : Reviews.ProdID - FK: ProdPC.PCID
PK : Reviews.ProdID - FK: ProdHW.HWID
PK : Reviews.ProdID - FK: ProdSW.SWID
No the link is:
PK : Reviews.ProdID - FK: Product.Id
PK : Reviews.ProdID - FK: Product.Id
PK : Reviews.ProdID - FK: Product.Id
This is because
PK: ProdPC.PCID - FK Product.Id
PK: ProdHW.HWI - FK Product.Id
PK: ProdSW.SWID- FK Product.Id
|||
i think u say that because u think i used Scope_Identity()? for now i dunno how to use that. so what i done is 1 Reviews Table linking to 3 Products Table (1 for PC, HW & SW) so Reviews.ProdID to ProdXX.ID. is that also ok?
Here is a SQL2005 script with some of the tables and a stored procedure for inserting a record showing how to use Scope_Identity()
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductSoftware]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ProductSoftware](
[Id] [int] NOT NULL,
[Category] [nvarchar](50) NOT NULL,
[Description] [nvarchar](250) NOT NULL,
[Rating] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ProductSoftware] PRIMARY KEY CLUSTERED
([Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Non-identity primary key from Product.Id' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'ProductSoftware', @.level2type=N'COLUMN', @.level2name=N'Id'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Product](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](250) NOT NULL,
[Price] [money] NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
([Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'a.k.a. Title' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'Product', @.level2type=N'COLUMN', @.level2name=N'Name'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductHardware]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ProductHardware](
[Id] [int] NOT NULL,
[Description] [varchar](max) NOT NULL,
CONSTRAINT [PK_ProductHardware] PRIMARY KEY CLUSTERED
([Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Non-identity product key from Product.Id' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'ProductHardware', @.level2type=N'COLUMN', @.level2name=N'Id'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductPc]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ProductPc](
[Id] [int] NOT NULL,
[Graphics] [nvarchar](50) NOT NULL,
[HDD] [nvarchar](50) NOT NULL,
[LCD] [nvarchar](50) NOT NULL,
[Processor] [nvarchar](50) NOT NULL,
[RAM] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ProductPc] PRIMARY KEY CLUSTERED
([Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Non-identity primary key from Product.Id' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'ProductPc', @.level2type=N'COLUMN', @.level2name=N'Id'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Review]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Review](
[Id] [int] NOT NULL,
[ProductId] [int] NOT NULL,
[ContentText] [nvarchar](max) NULL,
[Title] [nvarchar](50) NOT NULL,
[DateAdded] [datetime] NOT NULL CONSTRAINT [DF_Review_DateAdded] DEFAULT (getdate()),
CONSTRAINT [PK_Review] PRIMARY KEY CLUSTERED
([Id] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ProductHardware_Insert]')
AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'CREATE PROCEDURE [dbo].[usp_ProductHardware_Insert]
(
@.Description varchar (MAX),
@.Name nvarchar (250),
@.Price money,
@.RETURN INT OUTPUT,
@.IDENTITY INT OUTPUT
) AS
-- Purpose:
-- Insert record into Product and ProductHardware table
-- History:
-- 11Jun2007 CVC Original coding
SET NOCOUNT ON
INSERT INTO Product(Name, Price) VALUES (@.Name, @.Price)
SELECT @.IDENTITY = SCOPE_IDENTITY()
INSERT INTO ProductHardware(Id, Description)
VALUES ( @.IDENTITY, @.Description)
SELECT @.RETURN = @.@.error
RETURN
----- this is the end ------
'
END|||
i think i will need more knowledge about Stored Procedures and Scope Identity. any tutorials about that?
|||There are some good stored procedure tutorials at
http://www.codeproject.com/database/hkstoredproc.asp (Everything you wanted to know about Stored Procedures)