Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: 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 :

    Code:
    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 B
    Attached Thumbnails Attached Thumbnails Accounts.bmp  

  2. #2
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Table Structure

    Simple, 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

  3. #3
    Join Date
    Feb 2004
    Posts
    134
    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

  4. #4
    Join Date
    Feb 2004
    Posts
    134
    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?

    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •