How can I setup a table structure for the diagram shown in the attached bitmap?
1) I need to create a product using labour and materials.
2) I need to create hardware using labour and materials.
3) I need to be able to include some hardware in some products.
4) Some materials in the product are made of a culmination of materials. E.G., Concrete is made of sand, stone, and cement.
Any suggestions?
So far I have :
USE NORTHWIND
Create Table tbProducts (
ProductID int NOT NULL,
Product varchar(50)
)
go
ALTER TABLE tbProducts
ADD CONSTRAINT tbProducts_pk PRIMARY KEY (ProductID)
GO
CREATE Table tbMaterials (
MaterialID int NOT NULL,
Material varchar (50)
)
GO
ALTER TABLE tbMaterials
ADD CONSTRAINT tbMaterials_pk PRIMARY KEY (MaterialID)
GO
CREATE Table tbLabour (
LabourCode char (2) NOT NULL,
Labour varchar(50)
)
GO
ALTER TABLE tbLabour
ADD CONSTRAINT tbLabour_pk PRIMARY KEY (LabourCode)
GO
CREATE Table tbProductMaterials (
fkProductID int NOT NULL,
fkMaterialID int NOT NULL,
Quantity Float NOT NULL
)
GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterials_pk PRIMARY KEY (fkProductID, fkMaterialID)
GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterils_fk FOREIGN KEY (fkMaterialID)
REFERENCES tbMaterials (MaterialID)
GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterils_Product_fk FOREIGN KEY (fkProductID)
REFERENCES tbProducts (ProductID)
GO
CREATE TABLE tbProductLabour (
fkProductID int NOT NULL,
fkLabourCode char(2) NOT NULL,
Manpower int NULL DEFAULT(0),
Hours float NULL DEFAULT(0.0)
)
GO
ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_pk PRIMARY KEY (fkProductID, fkLabourCode)
GO
ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_fk FOREIGN KEY (fkLabourCode)
REFERENCES tbLabour (LabourCode)
GO
ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_Product_fk FOREIGN KEY (fkProductID)
REFERENCES tbProducts (ProductID)
GO
CREATE TABLE tbHardware (
HardwareID int NOT NULL,
Hardware varchar(50)
)
go
ALTER TABLE tbHardware
ADD CONSTRAINT tbHardware_pk PRIMARY KEY (HardwareID)
GO
CREATE Table tbHardwareMaterials (
fkHardwareID int NOT NULL,
fkMaterialID int NOT NULL,
Quantity Float NOT NULL
)
GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_pk PRIMARY KEY (fkHardwareID, fkMaterialID)
GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_fk FOREIGN KEY (fkMaterialID)
REFERENCES tbMaterials (MaterialID)
GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_Hardware_fk FOREIGN KEY (fkHardwareID)
REFERENCES tbHardware (HardwareID)
GO
CREATE TABLE tbHardwareLabour (
fkHardwareID int NOT NULL,
fkLabourCode char(2) NOT NULL,
Manpower int NULL DEFAULT(0),
Hours float NULL DEFAULT(0.0)
)
GO
ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_pk PRIMARY KEY (fkHardwareID, fkLabourCode)
GO
ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_fk FOREIGN KEY (fkLabourCode)
REFERENCES tbLabour (LabourCode)
GO
ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_Product_fk FOREIGN KEY (fkHardwareID)
REFERENCES tbHardware (HardwareID)
GO
This table structure does not include anything about accounts and item 4) making materials from 1 or more other materials (E.G., Concrete).
Any hints how to incorporate these outstanding items?
Mike BSimple, Just add a ID field. You dont have to show the field in the a form or to a user. You know it's a field that can be used to reference info and link to the other tabls.
CREATE Table tbMaterials
(MaterialID int NOT NULL,
Material varchar (50)
ID int(4) etc|||Very sorry about my ignorance, but could you explain how the ID field(s) would help me or how your suggesting I use them? I don't think I understand what your suggesting.
Mike B|||Here is my solution. I am not sure how well it would work so if you (the gurus) could compile the tables I have and let me know your thoughs?
The whole structure has to meet the criterial outlined in my original post.
Please, I could use some advice?
CREATE TABLE [dbo].[tbAccountCategories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[Category] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbConcreteMixDesigns] (
[MixID] [int] NOT NULL ,
[Mix] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbHardware] (
[HardwareID] [int] NOT NULL ,
[Hardware] [varchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbLabour] (
[LabourID] [int] NOT NULL ,
[Labour] [varchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbMaterials] (
[MaterialID] [int] NOT NULL ,
[Material] [varchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbProductTypes] (
[TypeID] [int] NOT NULL ,
[Type] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbAccounts] (
[AccountID] [char] (2) NOT NULL ,
[fkCategoryID] [int] NOT NULL ,
[Account] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbHardwareLabour] (
[fkHardwareID] [int] NOT NULL ,
[fkLabourCode] [int] NOT NULL ,
[Manpower] [int] NULL ,
[Hours] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbHardwareMaterials] (
[fkHardwareID] [int] NOT NULL ,
[fkMaterialID] [int] NOT NULL ,
[Quantity] [float] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbMixMaterials] (
[fkMixId] [int] NOT NULL ,
[fkMaterialID] [int] NOT NULL ,
[Quantity] [float] NOT NULL ,
[fkUnits] [char] (10) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbProducts] (
[ProductID] [int] NOT NULL ,
[fkTypeID] [int] NOT NULL ,
[Product] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbLabourAccounts] (
[fkAccountID] [char] (2) NOT NULL ,
[fkLabourID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbMaterialAccounts] (
[fkAccountID] [char] (2) NOT NULL ,
[fkMaterialID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbProductTemplates] (
[TemplateID] [char] (10) NOT NULL ,
[fkProductID] [int] NOT NULL ,
[Template] [char] (50) NOT NULL ,
[fkMixID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbProductLabour] (
[fkTemplateID] [char] (10) NOT NULL ,
[fkLabourCode] [int] NOT NULL ,
[Manpower] [int] NULL ,
[Hours] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbTemplateHardware] (
[fkTemplateID] [char] (10) NOT NULL ,
[fkHardwareID] [int] NOT NULL ,
[Quantity] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbTemplateMaterials] (
[fkTemplatetID] [char] (10) NOT NULL ,
[fkMaterialID] [int] NOT NULL ,
[Quantity] [float] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbAccountCategories] WITH NOCHECK ADD
CONSTRAINT [PK_tbAccountCategories] PRIMARY KEY CLUSTERED
(
[CategoryID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbConcreteMixDesigns] WITH NOCHECK ADD
CONSTRAINT [PK_tbConcreteMixDesigns] PRIMARY KEY CLUSTERED
(
[MixID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbHardware] WITH NOCHECK ADD
CONSTRAINT [tbHardware_pk] PRIMARY KEY CLUSTERED
(
[HardwareID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbLabour] WITH NOCHECK ADD
CONSTRAINT [tbLabour_pk] PRIMARY KEY CLUSTERED
(
[LabourID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbMaterials] WITH NOCHECK ADD
CONSTRAINT [tbMaterials_pk] PRIMARY KEY CLUSTERED
(
[MaterialID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbProductTypes] WITH NOCHECK ADD
CONSTRAINT [PK_tbProductTypes] PRIMARY KEY CLUSTERED
(
[TypeID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbAccounts] WITH NOCHECK ADD
CONSTRAINT [PK_tbAccounts] PRIMARY KEY CLUSTERED
(
[AccountID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbHardwareLabour] WITH NOCHECK ADD
CONSTRAINT [tbHardwareLabour_pk] PRIMARY KEY CLUSTERED
(
[fkHardwareID],
[fkLabourCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbHardwareMaterials] WITH NOCHECK ADD
CONSTRAINT [tbHardwareMaterials_pk] PRIMARY KEY CLUSTERED
(
[fkHardwareID],
[fkMaterialID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbMixMaterials] WITH NOCHECK ADD
CONSTRAINT [PK_tbMixMaterials] PRIMARY KEY CLUSTERED
(
[fkMixId],
[fkMaterialID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbProducts] WITH NOCHECK ADD
CONSTRAINT [tbProducts_pk] PRIMARY KEY CLUSTERED
(
[ProductID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbLabourAccounts] WITH NOCHECK ADD
CONSTRAINT [PK_tbLabourAccounts] PRIMARY KEY CLUSTERED
(
[fkAccountID],
[fkLabourID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbMaterialAccounts] WITH NOCHECK ADD
CONSTRAINT [PK_tbMaterialAccounts] PRIMARY KEY CLUSTERED
(
[fkAccountID],
[fkMaterialID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbProductTemplates] WITH NOCHECK ADD
CONSTRAINT [PK_tbProductTemplates] PRIMARY KEY CLUSTERED
(
[TemplateID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbProductLabour] WITH NOCHECK ADD
CONSTRAINT [tbProductLabour_pk] PRIMARY KEY CLUSTERED
(
[fkTemplateID],
[fkLabourCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbTemplateHardware] WITH NOCHECK ADD
CONSTRAINT [PK_tbTemplateHardware] PRIMARY KEY CLUSTERED
(
[fkTemplateID],
[fkHardwareID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbTemplateMaterials] WITH NOCHECK ADD
CONSTRAINT [tbProductMaterials_pk] PRIMARY KEY CLUSTERED
(
[fkTemplatetID],
[fkMaterialID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbHardwareLabour] ADD
CONSTRAINT [DF__tbHardwar__Manpo__0BC6C43E] DEFAULT (0) FOR [Manpower],
CONSTRAINT [DF__tbHardwar__Hours__0CBAE877] DEFAULT (0.0) FOR [Hours]
GO
ALTER TABLE [dbo].[tbProductLabour] ADD
CONSTRAINT [DF__tbProduct__Manpo__00551192] DEFAULT (0) FOR [Manpower],
CONSTRAINT [DF__tbProduct__Hours__014935CB] DEFAULT (0.0) FOR [Hours]
GO
ALTER TABLE [dbo].[tbTemplateHardware] ADD
CONSTRAINT [DF_tbTemplateHardware_Quantity] DEFAULT (0) FOR [Quantity]
GO
ALTER TABLE [dbo].[tbTemplateMaterials] ADD
CONSTRAINT [DF_tbTemplateMaterials_Quantity] DEFAULT (0) FOR [Quantity]
GO
ALTER TABLE [dbo].[tbAccounts] ADD
CONSTRAINT [FK_tbAccounts_tbAccountCategories] FOREIGN KEY
(
[fkCategoryID]
) REFERENCES [dbo].[tbAccountCategories] (
[CategoryID]
)
GO
ALTER TABLE [dbo].[tbHardwareLabour] ADD
CONSTRAINT [tbHardwareLabour_fk] FOREIGN KEY
(
[fkLabourCode]
) REFERENCES [dbo].[tbLabour] (
[LabourID]
),
CONSTRAINT [tbHardwareLabour_Product_fk] FOREIGN KEY
(
[fkHardwareID]
) REFERENCES [dbo].[tbHardware] (
[HardwareID]
)
GO
ALTER TABLE [dbo].[tbHardwareMaterials] ADD
CONSTRAINT [tbHardwareMaterials_fk] FOREIGN KEY
(
[fkMaterialID]
) REFERENCES [dbo].[tbMaterials] (
[MaterialID]
),
CONSTRAINT [tbHardwareMaterials_Hardware_fk] FOREIGN KEY
(
[fkHardwareID]
) REFERENCES [dbo].[tbHardware] (
[HardwareID]
)
GO
ALTER TABLE [dbo].[tbMixMaterials] ADD
CONSTRAINT [FK_tbMixMaterials_tbConcreteMixDesigns] FOREIGN KEY
(
[fkMixId]
) REFERENCES [dbo].[tbConcreteMixDesigns] (
[MixID]
),
CONSTRAINT [FK_tbMixMaterials_tbMaterials] FOREIGN KEY
(
[fkMaterialID]
) REFERENCES [dbo].[tbMaterials] (
[MaterialID]
)
GO
ALTER TABLE [dbo].[tbProducts] ADD
CONSTRAINT [FK_tbProducts_tbProductTypes] FOREIGN KEY
(
[fkTypeID]
) REFERENCES [dbo].[tbProductTypes] (
[TypeID]
)
GO
ALTER TABLE [dbo].[tbLabourAccounts] ADD
CONSTRAINT [FK_tbLabourAccounts_tbAccounts] FOREIGN KEY
(
[fkAccountID]
) REFERENCES [dbo].[tbAccounts] (
[AccountID]
),
CONSTRAINT [FK_tbLabourAccounts_tbLabour] FOREIGN KEY
(
[fkLabourID]
) REFERENCES [dbo].[tbLabour] (
[LabourID]
)
GO
ALTER TABLE [dbo].[tbMaterialAccounts] ADD
CONSTRAINT [FK_tbMaterialAccounts_tbAccounts] FOREIGN KEY
(
[fkAccountID]
) REFERENCES [dbo].[tbAccounts] (
[AccountID]
),
CONSTRAINT [FK_tbMaterialAccounts_tbMaterials] FOREIGN KEY
(
[fkMaterialID]
) REFERENCES [dbo].[tbMaterials] (
[MaterialID]
)
GO
ALTER TABLE [dbo].[tbProductTemplates] ADD
CONSTRAINT [FK_tbProductTemplates_tbConcreteMixDesigns] FOREIGN KEY
(
[fkMixID]
) REFERENCES [dbo].[tbConcreteMixDesigns] (
[MixID]
),
CONSTRAINT [FK_tbProductTemplates_tbProducts] FOREIGN KEY
(
[fkProductID]
) REFERENCES [dbo].[tbProducts] (
[ProductID]
)
GO
ALTER TABLE [dbo].[tbProductLabour] ADD
CONSTRAINT [FK_tbProductLabour_tbProductTemplates] FOREIGN KEY
(
[fkTemplateID]
) REFERENCES [dbo].[tbProductTemplates] (
[TemplateID]
),
CONSTRAINT [tbProductLabour_fk] FOREIGN KEY
(
[fkLabourCode]
) REFERENCES [dbo].[tbLabour] (
[LabourID]
)
GO
ALTER TABLE [dbo].[tbTemplateHardware] ADD
CONSTRAINT [FK_tbTemplateHardware_tbHardware] FOREIGN KEY
(
[fkHardwareID]
) REFERENCES [dbo].[tbHardware] (
[HardwareID]
),
CONSTRAINT [FK_tbTemplateHardware_tbProductTemplates] FOREIGN KEY
(
[fkTemplateID]
) REFERENCES [dbo].[tbProductTemplates] (
[TemplateID]
)
GO
ALTER TABLE [dbo].[tbTemplateMaterials] ADD
CONSTRAINT [FK_tbTemplateMaterials_tbProductTemplates] FOREIGN KEY
(
[fkTemplatetID]
) REFERENCES [dbo].[tbProductTemplates] (
[TemplateID]
),
CONSTRAINT [tbProductMaterils_fk] FOREIGN KEY
(
[fkMaterialID]
) REFERENCES [dbo].[tbMaterials] (
[MaterialID]
)
GO
Thanks in advance!
Mike B
in florida,
|||Try posting on the SQL Server SMO/DMO forum with the text of the error and a description of the steps you took before you received it. There might be something wrong with your installation though. Have you tried installing on a different machine or reinstalling? Did you get any errors during the initial installation?