Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 :-o wizard...or nightmare!

Author  Topic 

jhermiz

3564 Posts

Posted - 2003-12-18 : 09:12:34
I've asked this before with not too much help, not being anyone's
fault but my own for lack of requirements and definition as to what I'm looking
to solve.

I have built an estimating tool for our sales guys. The main thing to understand
is 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 one
to many from any of these tables to a Components table.

So the structure looks like this

+Proposal (Main table)
-Transports
TComponents
-Stations
SComponents
-Pallets
PComponents

That is one proposal can have any number of transports / pallets / stations in which
each 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 similiar
to 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. Basically
the categories table holds an identifier and a string, to me its like some sort of recursive
table with a CategoryID, a ParentID, and the actual Category...the other table ComponentCategories
associates a Component with a category.

Here is the categories table scripted
Categories

CREATE 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 box
which 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 it
to 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 transport
we recommened you will need a conveyor with your transport. Then the user selects a specific
conveyor. From there it should ask with the conveyor you have chosen we recommend you will need
conveyor ball tables of this size with this quantity..allowing the user to select..and so on and so
on 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 where
this is going. I have told them over and over that this is a huge change, and that I cannot
handle 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 end
because I dont know what structure I need to be looking at or creating. This is a very
complex application and hope that someone has the heart to give me a lead into what I am trying
to do. If anyone wants I'll post more info or anything.

Thanks for any help.

Jon

jhermiz

3564 Posts

Posted - 2003-12-18 : 14:07:38
I was afraid I'd get no responses.

I talked with my boss regarding this...

He mentioned the details arent too important on what we are trying to accomplish. We are basically trying to mimic something much like a configurator. For instance, you want to buy a car. You have a choice V8 / V6 / V4, if you pick V8 it comes with the correct transmission, oil filter, etc matched for a v8. It ensures that you get the right stuff!!! Thats the problem with what I am trying to do. It's very complex. My boss mentioned to come up with the actual logic, or structure behind this. He mentions that it doesnt matter what the details are right now, this configurator should work with ANY type of data. The problem right now he says is i need to define the hierarchy and not to worry about filling up the tables. He said maybe its decision tree making etc...

Does anyone want to do some consulting for a few days in Auburn Hills MI? I'm not even kidding. If anyone is interested please let me know.

Jon
Go to Top of Page

shuklin
Starting Member

3 Posts

Posted - 2003-12-29 : 07:58:31
Hi Jon,

I found your posting and i think that my old work can help you.
In 1999 I work on Ukrainian government. We did very-very complex project. Almost Nothing was known about the specifications. It was because Ukraine is young democratic republic, so it has very dynamic lows. We develop electronic documents circulation system. System must work in distributed environment across 35 cities of Ukraine. Each 4 month we must change fields or entire documents. Of course also we must work with historical data. So we must process old and new documents from different sources in one DB at one time. Business logic can calculate one group of fields from another group within one document, or one document from group of another documents within DB.

The base idea is the universal DB structure. This DB structure is mathematically full so it can present any financial document.

Demo project with open sources located at http://www.microsoft.ru/offext/details.aspx?id=620
Unfortunately, there is only Russian documentation. But VBA sources and SQLs are in English))

If you are interested to get this demo then you should have Microsoft Passport to login into that site.

WBR,
PhD (AI), Dmitry Shuklin
http://eidolon.euro.ru/indexeng.htm



quote:
Originally posted by jhermiz

I was afraid I'd get no responses.

I talked with my boss regarding this...

He mentioned the details arent too important on what we are trying to accomplish. We are basically trying to mimic something much like a configurator. For instance, you want to buy a car. You have a choice V8 / V6 / V4, if you pick V8 it comes with the correct transmission, oil filter, etc matched for a v8. It ensures that you get the right stuff!!! Thats the problem with what I am trying to do. It's very complex. My boss mentioned to come up with the actual logic, or structure behind this. He mentions that it doesnt matter what the details are right now, this configurator should work with ANY type of data. The problem right now he says is i need to define the hierarchy and not to worry about filling up the tables. He said maybe its decision tree making etc...

Does anyone want to do some consulting for a few days in Auburn Hills MI? I'm not even kidding. If anyone is interested please let me know.

Jon


Go to Top of Page

jhermiz

3564 Posts

Posted - 2003-12-30 : 01:10:19
Thanks,

I would be very interested in looking, the problem is the entire site is in russian. I don't even know where to login or provide my passport. Is it possible you can upload the source or send me the source to my e-mail account, or any help that you think may work out for me.

Thanks again,
Jon
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-12-30 : 01:14:30
Jon, you mentioned some sample data for this thing. Can you post (or e-mail me) some sample data to play with? I'll see what I can come up with.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

shuklin
Starting Member

3 Posts

Posted - 2003-12-30 : 08:56:32
Hi Jon,

Try this link [url]http://www.microsoft.ru/offext/download.aspx?id=620[/url]

I am not sure that this link will work - MS have strange politics to their Passport. May be to download this sources You will need to be already logged in.

WBR,
Dmitry


quote:
Originally posted by jhermiz

Thanks,

I would be very interested in looking, the problem is the entire site is in russian. I don't even know where to login or provide my passport. Is it possible you can upload the source or send me the source to my e-mail account, or any help that you think may work out for me.

Thanks again,
Jon


Go to Top of Page

jhermiz

3564 Posts

Posted - 2003-12-30 : 15:07:58
Dmitry,

I don't think this will be much help. I downloaded from that link. It's an excel template and an access database. It's all in russian...very hard to understand whats going on. I couldnt even run anything.

Michael,

Sample data into which tables :)...there are a lot of them :(.

Jon
Go to Top of Page

jhermiz

3564 Posts

Posted - 2003-12-30 : 15:15:38
quote:
Originally posted by MichaelP

Jon, you mentioned some sample data for this thing. Can you post (or e-mail me) some sample data to play with? I'll see what I can come up with.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>



Michael,

I'm even keen with working online (via e-mail) if it helps you out. I would be willing to have the company send you a check for your efforts? What are your thoughts...I know working online might be difficult, but I'm running into some dead ends here.

Jon
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-30 : 16:16:52
quote:
My problem is I have all these tables setup...is this wizard going to mean more tables? Decision trees?
Jon, my initial observations are Yes. In order to provide intelligence for your users, you will need at least a table which defines the ComponentRelations as they apply to each category. Ie. If I choose component A for category B then the system needs know that component A applies to B componentcategory and has N potential child components with their known quantities.

So, I am seeing a table such as:
CREATE TABLE ComponentRelations
(
ParentComp
ParentCompCat
ChildComp
ChilCompQty
)
Then as the user select the parent component, this table would be queried to find n appropriate child components and their quantities. This process would continue until the tree is full and no child are left to assign.
Go to Top of Page
   

- Advertisement -