I've asked this before with not too much help, not being anyone'sfault but my own for lack of requirements and definition as to what I'm lookingto solve.I have built an estimating tool for our sales guys. The main thing to understandis a proposal (one table) can have many transports / stations / pallets (other tables)associated with it (one to many from proposal to any of these). Now a transport /station / pallet can have many components associated with them. This is a oneto many from any of these tables to a Components table.So the structure looks like this+Proposal (Main table) -Transports TComponents -Stations SComponents -Pallets PComponentsThat is one proposal can have any number of transports / pallets / stations in whicheach of these can have many components associated with them.So everything is ok here...to help you understand I have scripted these tables:Proposals table:CREATE TABLE [dbo].[Proposals] ( [ProposalID] [bigint] IDENTITY (1, 1) NOT NULL , [CustomerID] [bigint] NULL , [SiteID] [bigint] NULL , [CommissionID] [bigint] NULL , [ProposalSetID] [bigint] NULL , [SystemDescription] [text] COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [DeliveryDate] [datetime] NULL , [DueDate] [datetime] NULL , [OpenDate] [datetime] NULL , [Originator] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [CustomerRFQ] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [QuoteNumber] [bigint] NULL , [FilePath] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [FileName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [Link] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [ExcelSent] [bit] NULL , [AdditionalInfo] [text] COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [ModifiedOn] [datetime] NULL , [ModifiedBy] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [ProposalStatus] [tinyint] NULL , [CalcSheetCreatedOn] [datetime] NULL , [RevLevel] [int] NULL , [CountryCode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [StatusReason] [text] COLLATE SQL_Latin1_General_CP1_CS_AS NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
Transports Table:CREATE TABLE [dbo].[Transports] ( [TransportID] [bigint] IDENTITY (1, 1) NOT NULL , [ProposalID] [bigint] NOT NULL , [TDescription] [text] COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [TFunction] [text] COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [TAssumption] [text] COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [TOrientation] [text] COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [CreatedBy] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [CreatedOn] [datetime] NULL , [ModifiedBy] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [ModifiedOn] [datetime] NULL , [TEndPos] [smallint] NULL , [TPos] AS ('1.' + convert(varchar(48),[TEndPos])) , [TSent] [bit] NULL , [CostUnit] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
Transports Components Table:CREATE TABLE [dbo].[TComponents] ( [TCompID] [bigint] IDENTITY (1, 1) NOT NULL , [TransportID] [bigint] NULL , [TComp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [Quantity] [int] NULL , [N] [int] NULL , [AdditionalDescription] [text] COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [ExcelSent] [bit] NULL , [PriceSource] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [CreatedBy] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [CreatedOn] [datetime] NULL , [ModifiedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [ModifiedOn] [datetime] NULL , [TCPos] [int] NULL , [TCompDescription] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [Edited] [bit] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
The other tables stations, pallets, and their components are very much similiarto the transports and transport components table so I did not post these. The following is the components table:Components:CREATE TABLE [dbo].[Components] ( [ComponentID] [bigint] IDENTITY (1, 1) NOT NULL , [Component] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [EnglishDescription] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [GermanDescription] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [AdditionalInfo] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [CategoryID] [bigint] NULL , [FriendlyEnglishDescription] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ) ON [PRIMARY]GO
I hope I havent confused anyone so far. Now after designing the system...most people wanted a simple way to find a component because we have sooooo many!So I created a table of categories and a table of ComponentCategories. Basicallythe categories table holds an identifier and a string, to me its like some sort of recursivetable with a CategoryID, a ParentID, and the actual Category...the other table ComponentCategoriesassociates a Component with a category.Here is the categories table scriptedCategoriesCREATE TABLE [dbo].[Categories] ( [CategoryID] [bigint] IDENTITY (1, 1) NOT NULL , [ParentID] [bigint] NULL , [Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL , [CategoryInfo] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ) ON [PRIMARY]GO
And also the ComponentCategories table scripted:CREATE TABLE [dbo].[CategoriesByComponentType] ( [CategoryByComponentID] [bigint] IDENTITY (1, 1) NOT NULL , [CategoryID] [bigint] NULL , [ComponentID] [bigint] NULL ) ON [PRIMARY]GO
If anyone needs sample records for these two tables and how they work please let me know.Now with these two tables I was able to have users select some categories from a combo boxwhich then basically narrowed down to the components.Everything is working fine...But now management wants some sort of wizard at the highest level. They basically want itto lead you to the complete product when you are done. So for instance, lets say an end user wants to add a transport to a system. It should start by something like ok you want a transportwe recommened you will need a conveyor with your transport. Then the user selects a specificconveyor. From there it should ask with the conveyor you have chosen we recommend you will needconveyor ball tables of this size with this quantity..allowing the user to select..and so on and soon until the transport is fully built.My problem is I have all these tables setup...is this wizard going to mean more tables?Decision trees? I am the lone developer that is why I am sort of scared as to wherethis is going. I have told them over and over that this is a huge change, and that I cannothandle it on my own. But I have been told to keep working on it even if it takes long.I was just recently told to come up with the structure to support it...but im in a dead endbecause I dont know what structure I need to be looking at or creating. This is a verycomplex application and hope that someone has the heart to give me a lead into what I am tryingto do. If anyone wants I'll post more info or anything.Thanks for any help.Jon