I can really use some help.Table structure

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

I am getting error Subreport could not be shown

I am trying to call a subreport in master report, if i try to run the sub report individually by passing hardcoded nonqueried values in the parameters it runs fine, but when i use it as subreport in a master report, it shows the error: subreport could not be shown,

In my subreport i am using the table control to populate all fields is there something to do with table control or i am calling the subreport control in the detail pane of main report, and all main reports fields are in the group header.

Please help.

error info: from error list:

Warning 1 [rsErrorExecutingSubreport] An error occurred while executing the subreport ‘subreport1’: One or more parameters required to run the report have not been specified. c:\aspnet\ccsreports2005\ccsreports2005\rptNewIssueHRT.rdl 0 0

Thank you all very much for the info.

Have you mapped the parameters in the master report to the parameters in the subreport? You do that through the properties screen of the subreport object.|||

Hi Adam,

I did mapped the parameters via parameter tab from subreport control.

My subreport is using a table control to populate all columns and there result.

If i run the sub report by itself individually, it runs fine with hardcoded values in it, but when i reference the same thru a subreport control via master report i am getting the error subreport could not be shown.

Please help is there something i am doing a mistake. and also do i need to have the subreports parameters also in master report.

Since under my master report i only have 1 parameter, thats all defined to pull it data, once that data gets in want to use those master datas value to pull the subreports data.

Master report uses stored proc which expects only one parameter, that is what i defined under parameter collection on layout of report.

Thank you very much.

|||So if your master report only has 1 parameter than how did you perform the mapping from the master report to the subreport parameters?|||

I got that error when the subreport was bigger than the textbox in which I placed it. I've also gotten the error when the datasource wasn't quite right, but it looks like you've covered that.

Hope this helps,

Don

|||

Yes, i found the problem yesterday. i am getting the parameter related columns in the dataset but did'nt place it on the report as a column since it is holding ID's in it.

after placing teh column and made it hidden true, then it is able to pull the data on subreport.

Thank you.