You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Jason Efting <de...@yahoo.com> on 2003/05/06 10:11:08 UTC
Design Question
Hi,Sorry if this is a bit off topic but I need some design advice. (It will be implemented in Tomcat)
I'm busy designing an intranet application for company. The application is
meant to provide a means wear by sales people can capture a product using
the company intranet portal.
The company has various products and each product has lot of details in
common with other products, instead of having a separate page for each
product I want to dynamically generate the pages that must be completed for
a product sale based on the fields required for a specific product.
This means that very product has a bunch of pages associated with it. The
fields that the sales person would have to complete on each page would also
depend on the product selected.
I designed the table structure below; the DDL script will create the tables
and relationships for you as well as insert sample data.
Overview of design
-----------------
PRODUCT - All the prodcuts that company sells.
PAGE - All the pages in the web application that is used to capture
information.
FIELDS - All the fields that may be captured with the validation errors.
PRODUCTPAGE - Bridgeing table linking everything.
Does anybody have any experience they can share with a design like the
above?
Do you see any problems with the design?
Has anybody ever seen a design like this being used in a production system?
Platform
----------
SQL Server 2000
Apache/Tomcat 4.x
Windows 2000 Server
Hopefully you get a clear picture of what I'm trying to accomplish. Please
let me know if anything is unclear.
Thanks
Jason
START OF SCRIPT
***************
--*******************
--CREATE TABLES
--*******************
CREATE TABLE [PRODUCT] (
[PROD_ID] [tinyint] NOT NULL ,
[PROD_CODE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PROD_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_PRODUCT] PRIMARY KEY CLUSTERED
(
[PROD_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [PAGE] (
[PAGE_ID] [tinyint] NOT NULL ,
[PAGE_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PAGE_PATH] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_PAGE] PRIMARY KEY CLUSTERED
(
[PAGE_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [FIELDS] (
[FIELD_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FIELD_ERRMESSAGE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[FIELD_ERREXPLANATION] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[FIELD_ERRFIX] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_FIELDS] PRIMARY KEY CLUSTERED
(
[FIELD_NAME]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [PRODUCTPAGE] (
[PROD_ID] [tinyint] NOT NULL ,
[PAGE_ID] [tinyint] NOT NULL ,
[FIELD_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_PRODUCTPAGE] PRIMARY KEY CLUSTERED
(
[PROD_ID],
[PAGE_ID],
[FIELD_NAME]
) ON [PRIMARY] ,
CONSTRAINT [FK_PRODUCTPAGE_FIELDS] FOREIGN KEY
(
[FIELD_NAME]
) REFERENCES [FIELDS] (
[FIELD_NAME]
),
CONSTRAINT [FK_PRODUCTPAGE_PAGE] FOREIGN KEY
(
[PAGE_ID]
) REFERENCES [PAGE] (
[PAGE_ID]
),
CONSTRAINT [FK_PRODUCTPAGE_PRODUCT] FOREIGN KEY
(
[PROD_ID]
) REFERENCES [PRODUCT] (
[PROD_ID]
)
) ON [PRIMARY]
GO
--*******************
--INSERT SAMPLE DATA
--*******************
--insert products
-----------------
INSERT PRODUCT VALUES (1, 'PET', 'Pet Tracking System')
INSERT PRODUCT VALUES (2, 'CHI', 'Child Monitor')
--insert pages
--------------
/*SELECT 'INSERT PAGE VALUES (' + cast(PAGE_ID as varchar) + ',''' +
PAGE_NAME
+ ''',''' + PAGE_PATH + ''')'
FROM PAGE*/
INSERT PAGE VALUES (1,'Personal Details','d:\www\capt\personaldet.asp')
INSERT PAGE VALUES (2,'Banking Details','d:\www\capt\bankdet.asp')
INSERT PAGE VALUES (3,'Pet Info','d:\www\capt\petinfo.asp')
INSERT PAGE VALUES (4,'Child Info','d:\www\capt\childinfo.asp')
--insert fields
---------------
/*SELECT 'INSERT FIELDS VALUES (''' + FIELD_NAME + ''',''' +
FIELD_ERRMESSAGE
+ ''',''' + FIELD_ERREXPLANATION + ''',''' + FIELD_ERRFIX + ''')'
FROM FIELDS*/
INSERT FIELDS VALUES ('BankName','Please enter.','Not completed.','Complete
bank name.')
INSERT FIELDS VALUES ('ChildName','Please enter.','Not completed.','Complete
child name.')
INSERT FIELDS VALUES ('MemberInitials','Please enter.','Not
completed.','Complete member initails.')
INSERT FIELDS VALUES ('MemberName','Please enter.','Not
completed.','Complete member surname.')
INSERT FIELDS VALUES ('MemberSurname','Please enter.','Not
completed.','Complete member surname.')
INSERT FIELDS VALUES ('PetName','Please enter.','Not completed.','Complete
pet name.')
--insert productpage
---------------
/*SELECT 'INSERT PRODUCTPAGE VALUES(' + cast(PROD_ID as varchar) + ',' +
cast(PAGE_ID as varchar)
+ ',''' + FIELD_NAME + ''')'
FROM PRODUCTPAGE*/
INSERT PRODUCTPAGE VALUES(1,1,'MemberInitials')
INSERT PRODUCTPAGE VALUES(1,1,'MemberName')
INSERT PRODUCTPAGE VALUES(1,1,'MemberSurname')
INSERT PRODUCTPAGE VALUES(1,2,'BankName')
INSERT PRODUCTPAGE VALUES(1,3,'PetName')
INSERT PRODUCTPAGE VALUES(2,1,'MemberInitials')
INSERT PRODUCTPAGE VALUES(2,1,'MemberName')
INSERT PRODUCTPAGE VALUES(2,1,'MemberSurname')
INSERT PRODUCTPAGE VALUES(2,2,'BankName')
INSERT PRODUCTPAGE VALUES(2,4,'ChildName')
--select product and it's field
/*select pro.prod_name, fie.field_name
from product pro
inner join productpage pg on pro.prod_id = pg.prod_id
inner join fields fie on pg.field_name = fie.field_name*/
***************
END OF SCRIPT
---------------------------------
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
Re: Design Question
Posted by Thomas Van de Velde <th...@tiscali.be>.
If you want to handle complex forms logic while maintaining a seperation
between presentation logic and business logic, I would recommend the XMLForm
module that comes with Cocoon 2.1. This module is available through the
Cocoon CVS repository or as a stand-alone component on
http://www.cocoonhive.org/xmlform/index.html
For a nice shopping cart (although I am not sure if this is what you ask), I
would refer to the MIT's Offbiz application: http://www.ofbiz.org/
----- Original Message -----
From: "Jason Efting" <de...@yahoo.com>
To: <to...@jakarta.apache.org>
Sent: Tuesday, May 06, 2003 10:11 AM
Subject: Design Question
> Hi,Sorry if this is a bit off topic but I need some design advice. (It
will be implemented in Tomcat)
> I'm busy designing an intranet application for company. The application is
> meant to provide a means wear by sales people can capture a product using
> the company intranet portal.
>
> The company has various products and each product has lot of details in
> common with other products, instead of having a separate page for each
> product I want to dynamically generate the pages that must be completed
for
> a product sale based on the fields required for a specific product.
>
> This means that very product has a bunch of pages associated with it. The
> fields that the sales person would have to complete on each page would
also
> depend on the product selected.
>
> I designed the table structure below; the DDL script will create the
tables
> and relationships for you as well as insert sample data.
>
> Overview of design
> -----------------
> PRODUCT - All the prodcuts that company sells.
> PAGE - All the pages in the web application that is used to capture
> information.
> FIELDS - All the fields that may be captured with the validation errors.
> PRODUCTPAGE - Bridgeing table linking everything.
>
> Does anybody have any experience they can share with a design like the
> above?
>
> Do you see any problems with the design?
>
> Has anybody ever seen a design like this being used in a production
system?
>
> Platform
> ----------
> SQL Server 2000
> Apache/Tomcat 4.x
> Windows 2000 Server
>
> Hopefully you get a clear picture of what I'm trying to accomplish. Please
> let me know if anything is unclear.
>
> Thanks
> Jason
>
> START OF SCRIPT
> ***************
>
> --*******************
> --CREATE TABLES
> --*******************
> CREATE TABLE [PRODUCT] (
> [PROD_ID] [tinyint] NOT NULL ,
> [PROD_CODE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PROD_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> CONSTRAINT [PK_PRODUCT] PRIMARY KEY CLUSTERED
> (
> [PROD_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [PAGE] (
> [PAGE_ID] [tinyint] NOT NULL ,
> [PAGE_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [PAGE_PATH] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> CONSTRAINT [PK_PAGE] PRIMARY KEY CLUSTERED
> (
> [PAGE_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [FIELDS] (
> [FIELD_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [FIELD_ERRMESSAGE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [FIELD_ERREXPLANATION] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [FIELD_ERRFIX] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> CONSTRAINT [PK_FIELDS] PRIMARY KEY CLUSTERED
> (
> [FIELD_NAME]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [PRODUCTPAGE] (
> [PROD_ID] [tinyint] NOT NULL ,
> [PAGE_ID] [tinyint] NOT NULL ,
> [FIELD_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> CONSTRAINT [PK_PRODUCTPAGE] PRIMARY KEY CLUSTERED
> (
> [PROD_ID],
> [PAGE_ID],
> [FIELD_NAME]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_PRODUCTPAGE_FIELDS] FOREIGN KEY
> (
> [FIELD_NAME]
> ) REFERENCES [FIELDS] (
> [FIELD_NAME]
> ),
> CONSTRAINT [FK_PRODUCTPAGE_PAGE] FOREIGN KEY
> (
> [PAGE_ID]
> ) REFERENCES [PAGE] (
> [PAGE_ID]
> ),
> CONSTRAINT [FK_PRODUCTPAGE_PRODUCT] FOREIGN KEY
> (
> [PROD_ID]
> ) REFERENCES [PRODUCT] (
> [PROD_ID]
> )
> ) ON [PRIMARY]
> GO
>
>
>
>
> --*******************
> --INSERT SAMPLE DATA
> --*******************
>
> --insert products
> -----------------
> INSERT PRODUCT VALUES (1, 'PET', 'Pet Tracking System')
> INSERT PRODUCT VALUES (2, 'CHI', 'Child Monitor')
>
> --insert pages
> --------------
>
> /*SELECT 'INSERT PAGE VALUES (' + cast(PAGE_ID as varchar) + ',''' +
> PAGE_NAME
> + ''',''' + PAGE_PATH + ''')'
> FROM PAGE*/
>
> INSERT PAGE VALUES (1,'Personal Details','d:\www\capt\personaldet.asp')
> INSERT PAGE VALUES (2,'Banking Details','d:\www\capt\bankdet.asp')
> INSERT PAGE VALUES (3,'Pet Info','d:\www\capt\petinfo.asp')
> INSERT PAGE VALUES (4,'Child Info','d:\www\capt\childinfo.asp')
>
> --insert fields
> ---------------
>
> /*SELECT 'INSERT FIELDS VALUES (''' + FIELD_NAME + ''',''' +
> FIELD_ERRMESSAGE
> + ''',''' + FIELD_ERREXPLANATION + ''',''' + FIELD_ERRFIX + ''')'
> FROM FIELDS*/
>
> INSERT FIELDS VALUES ('BankName','Please enter.','Not
completed.','Complete
> bank name.')
> INSERT FIELDS VALUES ('ChildName','Please enter.','Not
completed.','Complete
> child name.')
> INSERT FIELDS VALUES ('MemberInitials','Please enter.','Not
> completed.','Complete member initails.')
> INSERT FIELDS VALUES ('MemberName','Please enter.','Not
> completed.','Complete member surname.')
> INSERT FIELDS VALUES ('MemberSurname','Please enter.','Not
> completed.','Complete member surname.')
> INSERT FIELDS VALUES ('PetName','Please enter.','Not completed.','Complete
> pet name.')
>
> --insert productpage
> ---------------
> /*SELECT 'INSERT PRODUCTPAGE VALUES(' + cast(PROD_ID as varchar) + ',' +
> cast(PAGE_ID as varchar)
> + ',''' + FIELD_NAME + ''')'
> FROM PRODUCTPAGE*/
>
> INSERT PRODUCTPAGE VALUES(1,1,'MemberInitials')
> INSERT PRODUCTPAGE VALUES(1,1,'MemberName')
> INSERT PRODUCTPAGE VALUES(1,1,'MemberSurname')
> INSERT PRODUCTPAGE VALUES(1,2,'BankName')
> INSERT PRODUCTPAGE VALUES(1,3,'PetName')
> INSERT PRODUCTPAGE VALUES(2,1,'MemberInitials')
> INSERT PRODUCTPAGE VALUES(2,1,'MemberName')
> INSERT PRODUCTPAGE VALUES(2,1,'MemberSurname')
> INSERT PRODUCTPAGE VALUES(2,2,'BankName')
> INSERT PRODUCTPAGE VALUES(2,4,'ChildName')
>
> --select product and it's field
> /*select pro.prod_name, fie.field_name
> from product pro
> inner join productpage pg on pro.prod_id = pg.prod_id
> inner join fields fie on pg.field_name = fie.field_name*/
>
> ***************
> END OF SCRIPT
>
>
> ---------------------------------
> Do you Yahoo!?
> The New Yahoo! Search - Faster. Easier. Bingo.
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Design Question
Posted by Jason Efting <de...@yahoo.com>.
Thanks for your reply. When I say capture a product I mean that the sales person will have to complete a serious of online forms. (Capturing the clients details in other words)
Not really a shopping cart effect. The sales person will select a product and then complete it while the customer is on the phone.
The questions I'm really trying to get answered here is whether the database design would work. I know I can make it work but I would like to know whether there are better ways. How would you have implemented this application?
Does that make it clear?
Kwok Peng Tuck <pe...@makmal.net> wrote:Jason Efting wrote:
>Hi,Sorry if this is a bit off topic but I need some design advice. (It will be implemented in Tomcat)
>
You could attach [OT] to the subject if you want people to ignore the
off topic message .
About your problem, there isn't really a problem with what you want to
do, but I am unclear about what you imply when you say 'to provide a
means wear by sales people can capture a product' . Something like a
shopping cart ? Click to see item , like it then add to cart ?
>I'm busy designing an intranet application for company. The application is
>meant to provide a means wear by sales people can capture a product using
>the company intranet portal.
>
>
>The company has various products and each product has lot of details in
>common with other products, instead of having a separate page for each
>product I want to dynamically generate the pages that must be completed for
>a product sale based on the fields required for a specific product.
>
>This means that very product has a bunch of pages associated with it. The
>fields that the sales person would have to complete on each page would also
>depend on the product selected.
>
>I designed the table structure below; the DDL script will create the tables
>and relationships for you as well as insert sample data.
>
>Overview of design
>-----------------
>PRODUCT - All the prodcuts that company sells.
>PAGE - All the pages in the web application that is used to capture
>information.
>FIELDS - All the fields that may be captured with the validation errors.
>PRODUCTPAGE - Bridgeing table linking everything.
>
>Does anybody have any experience they can share with a design like the
>above?
>
>Do you see any problems with the design?
>
>Has anybody ever seen a design like this being used in a production system?
>
>Platform
>----------
>SQL Server 2000
>Apache/Tomcat 4.x
>Windows 2000 Server
>
>Hopefully you get a clear picture of what I'm trying to accomplish. Please
>let me know if anything is unclear.
>
>Thanks
>Jason
>
>START OF SCRIPT
>***************
>
>--*******************
>--CREATE TABLES
>--*******************
>CREATE TABLE [PRODUCT] (
> [PROD_ID] [tinyint] NOT NULL ,
> [PROD_CODE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PROD_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> CONSTRAINT [PK_PRODUCT] PRIMARY KEY CLUSTERED
> (
> [PROD_ID]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>
>CREATE TABLE [PAGE] (
> [PAGE_ID] [tinyint] NOT NULL ,
> [PAGE_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PAGE_PATH] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> CONSTRAINT [PK_PAGE] PRIMARY KEY CLUSTERED
> (
> [PAGE_ID]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>
>CREATE TABLE [FIELDS] (
> [FIELD_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [FIELD_ERRMESSAGE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL ,
> [FIELD_ERREXPLANATION] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
>NOT NULL ,
> [FIELD_ERRFIX] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL ,
> CONSTRAINT [PK_FIELDS] PRIMARY KEY CLUSTERED
> (
> [FIELD_NAME]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>
>CREATE TABLE [PRODUCTPAGE] (
> [PROD_ID] [tinyint] NOT NULL ,
> [PAGE_ID] [tinyint] NOT NULL ,
> [FIELD_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> CONSTRAINT [PK_PRODUCTPAGE] PRIMARY KEY CLUSTERED
> (
> [PROD_ID],
> [PAGE_ID],
> [FIELD_NAME]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_PRODUCTPAGE_FIELDS] FOREIGN KEY
> (
> [FIELD_NAME]
> ) REFERENCES [FIELDS] (
> [FIELD_NAME]
> ),
> CONSTRAINT [FK_PRODUCTPAGE_PAGE] FOREIGN KEY
> (
> [PAGE_ID]
> ) REFERENCES [PAGE] (
> [PAGE_ID]
> ),
> CONSTRAINT [FK_PRODUCTPAGE_PRODUCT] FOREIGN KEY
> (
> [PROD_ID]
> ) REFERENCES [PRODUCT] (
> [PROD_ID]
> )
>) ON [PRIMARY]
>GO
>
>
>
>
>--*******************
>--INSERT SAMPLE DATA
>--*******************
>
>--insert products
>-----------------
>INSERT PRODUCT VALUES (1, 'PET', 'Pet Tracking System')
>INSERT PRODUCT VALUES (2, 'CHI', 'Child Monitor')
>
>--insert pages
>--------------
>
>/*SELECT 'INSERT PAGE VALUES (' + cast(PAGE_ID as varchar) + ',''' +
>PAGE_NAME
> + ''',''' + PAGE_PATH + ''')'
>FROM PAGE*/
>
>INSERT PAGE VALUES (1,'Personal Details','d:\www\capt\personaldet.asp')
>INSERT PAGE VALUES (2,'Banking Details','d:\www\capt\bankdet.asp')
>INSERT PAGE VALUES (3,'Pet Info','d:\www\capt\petinfo.asp')
>INSERT PAGE VALUES (4,'Child Info','d:\www\capt\childinfo.asp')
>
>--insert fields
>---------------
>
>/*SELECT 'INSERT FIELDS VALUES (''' + FIELD_NAME + ''',''' +
>FIELD_ERRMESSAGE
> + ''',''' + FIELD_ERREXPLANATION + ''',''' + FIELD_ERRFIX + ''')'
>FROM FIELDS*/
>
>INSERT FIELDS VALUES ('BankName','Please enter.','Not completed.','Complete
>bank name.')
>INSERT FIELDS VALUES ('ChildName','Please enter.','Not completed.','Complete
>child name.')
>INSERT FIELDS VALUES ('MemberInitials','Please enter.','Not
>completed.','Complete member initails.')
>INSERT FIELDS VALUES ('MemberName','Please enter.','Not
>completed.','Complete member surname.')
>INSERT FIELDS VALUES ('MemberSurname','Please enter.','Not
>completed.','Complete member surname.')
>INSERT FIELDS VALUES ('PetName','Please enter.','Not completed.','Complete
>pet name.')
>
>--insert productpage
>---------------
>/*SELECT 'INSERT PRODUCTPAGE VALUES(' + cast(PROD_ID as varchar) + ',' +
>cast(PAGE_ID as varchar)
> + ',''' + FIELD_NAME + ''')'
>FROM PRODUCTPAGE*/
>
>INSERT PRODUCTPAGE VALUES(1,1,'MemberInitials')
>INSERT PRODUCTPAGE VALUES(1,1,'MemberName')
>INSERT PRODUCTPAGE VALUES(1,1,'MemberSurname')
>INSERT PRODUCTPAGE VALUES(1,2,'BankName')
>INSERT PRODUCTPAGE VALUES(1,3,'PetName')
>INSERT PRODUCTPAGE VALUES(2,1,'MemberInitials')
>INSERT PRODUCTPAGE VALUES(2,1,'MemberName')
>INSERT PRODUCTPAGE VALUES(2,1,'MemberSurname')
>INSERT PRODUCTPAGE VALUES(2,2,'BankName')
>INSERT PRODUCTPAGE VALUES(2,4,'ChildName')
>
>--select product and it's field
>/*select pro.prod_name, fie.field_name
>from product pro
>inner join productpage pg on pro.prod_id = pg.prod_id
>inner join fields fie on pg.field_name = fie.field_name*/
>
>***************
>END OF SCRIPT
>
>
>---------------------------------
>Do you Yahoo!?
>The New Yahoo! Search - Faster. Easier. Bingo.
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
---------------------------------
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
Re: Design Question
Posted by Kwok Peng Tuck <pe...@makmal.net>.
Jason Efting wrote:
>Hi,Sorry if this is a bit off topic but I need some design advice. (It will be implemented in Tomcat)
>
You could attach [OT] to the subject if you want people to ignore the
off topic message .
About your problem, there isn't really a problem with what you want to
do, but I am unclear about what you imply when you say 'to provide a
means wear by sales people can capture a product' . Something like a
shopping cart ? Click to see item , like it then add to cart ?
>I'm busy designing an intranet application for company. The application is
>meant to provide a means wear by sales people can capture a product using
>the company intranet portal.
>
>
>The company has various products and each product has lot of details in
>common with other products, instead of having a separate page for each
>product I want to dynamically generate the pages that must be completed for
>a product sale based on the fields required for a specific product.
>
>This means that very product has a bunch of pages associated with it. The
>fields that the sales person would have to complete on each page would also
>depend on the product selected.
>
>I designed the table structure below; the DDL script will create the tables
>and relationships for you as well as insert sample data.
>
>Overview of design
>-----------------
>PRODUCT - All the prodcuts that company sells.
>PAGE - All the pages in the web application that is used to capture
>information.
>FIELDS - All the fields that may be captured with the validation errors.
>PRODUCTPAGE - Bridgeing table linking everything.
>
>Does anybody have any experience they can share with a design like the
>above?
>
>Do you see any problems with the design?
>
>Has anybody ever seen a design like this being used in a production system?
>
>Platform
>----------
>SQL Server 2000
>Apache/Tomcat 4.x
>Windows 2000 Server
>
>Hopefully you get a clear picture of what I'm trying to accomplish. Please
>let me know if anything is unclear.
>
>Thanks
>Jason
>
>START OF SCRIPT
>***************
>
>--*******************
>--CREATE TABLES
>--*******************
>CREATE TABLE [PRODUCT] (
> [PROD_ID] [tinyint] NOT NULL ,
> [PROD_CODE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PROD_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> CONSTRAINT [PK_PRODUCT] PRIMARY KEY CLUSTERED
> (
> [PROD_ID]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>
>CREATE TABLE [PAGE] (
> [PAGE_ID] [tinyint] NOT NULL ,
> [PAGE_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PAGE_PATH] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> CONSTRAINT [PK_PAGE] PRIMARY KEY CLUSTERED
> (
> [PAGE_ID]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>
>CREATE TABLE [FIELDS] (
> [FIELD_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [FIELD_ERRMESSAGE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL ,
> [FIELD_ERREXPLANATION] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
>NOT NULL ,
> [FIELD_ERRFIX] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL ,
> CONSTRAINT [PK_FIELDS] PRIMARY KEY CLUSTERED
> (
> [FIELD_NAME]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>
>CREATE TABLE [PRODUCTPAGE] (
> [PROD_ID] [tinyint] NOT NULL ,
> [PAGE_ID] [tinyint] NOT NULL ,
> [FIELD_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> CONSTRAINT [PK_PRODUCTPAGE] PRIMARY KEY CLUSTERED
> (
> [PROD_ID],
> [PAGE_ID],
> [FIELD_NAME]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_PRODUCTPAGE_FIELDS] FOREIGN KEY
> (
> [FIELD_NAME]
> ) REFERENCES [FIELDS] (
> [FIELD_NAME]
> ),
> CONSTRAINT [FK_PRODUCTPAGE_PAGE] FOREIGN KEY
> (
> [PAGE_ID]
> ) REFERENCES [PAGE] (
> [PAGE_ID]
> ),
> CONSTRAINT [FK_PRODUCTPAGE_PRODUCT] FOREIGN KEY
> (
> [PROD_ID]
> ) REFERENCES [PRODUCT] (
> [PROD_ID]
> )
>) ON [PRIMARY]
>GO
>
>
>
>
>--*******************
>--INSERT SAMPLE DATA
>--*******************
>
>--insert products
>-----------------
>INSERT PRODUCT VALUES (1, 'PET', 'Pet Tracking System')
>INSERT PRODUCT VALUES (2, 'CHI', 'Child Monitor')
>
>--insert pages
>--------------
>
>/*SELECT 'INSERT PAGE VALUES (' + cast(PAGE_ID as varchar) + ',''' +
>PAGE_NAME
> + ''',''' + PAGE_PATH + ''')'
>FROM PAGE*/
>
>INSERT PAGE VALUES (1,'Personal Details','d:\www\capt\personaldet.asp')
>INSERT PAGE VALUES (2,'Banking Details','d:\www\capt\bankdet.asp')
>INSERT PAGE VALUES (3,'Pet Info','d:\www\capt\petinfo.asp')
>INSERT PAGE VALUES (4,'Child Info','d:\www\capt\childinfo.asp')
>
>--insert fields
>---------------
>
>/*SELECT 'INSERT FIELDS VALUES (''' + FIELD_NAME + ''',''' +
>FIELD_ERRMESSAGE
> + ''',''' + FIELD_ERREXPLANATION + ''',''' + FIELD_ERRFIX + ''')'
>FROM FIELDS*/
>
>INSERT FIELDS VALUES ('BankName','Please enter.','Not completed.','Complete
>bank name.')
>INSERT FIELDS VALUES ('ChildName','Please enter.','Not completed.','Complete
>child name.')
>INSERT FIELDS VALUES ('MemberInitials','Please enter.','Not
>completed.','Complete member initails.')
>INSERT FIELDS VALUES ('MemberName','Please enter.','Not
>completed.','Complete member surname.')
>INSERT FIELDS VALUES ('MemberSurname','Please enter.','Not
>completed.','Complete member surname.')
>INSERT FIELDS VALUES ('PetName','Please enter.','Not completed.','Complete
>pet name.')
>
>--insert productpage
>---------------
>/*SELECT 'INSERT PRODUCTPAGE VALUES(' + cast(PROD_ID as varchar) + ',' +
>cast(PAGE_ID as varchar)
> + ',''' + FIELD_NAME + ''')'
>FROM PRODUCTPAGE*/
>
>INSERT PRODUCTPAGE VALUES(1,1,'MemberInitials')
>INSERT PRODUCTPAGE VALUES(1,1,'MemberName')
>INSERT PRODUCTPAGE VALUES(1,1,'MemberSurname')
>INSERT PRODUCTPAGE VALUES(1,2,'BankName')
>INSERT PRODUCTPAGE VALUES(1,3,'PetName')
>INSERT PRODUCTPAGE VALUES(2,1,'MemberInitials')
>INSERT PRODUCTPAGE VALUES(2,1,'MemberName')
>INSERT PRODUCTPAGE VALUES(2,1,'MemberSurname')
>INSERT PRODUCTPAGE VALUES(2,2,'BankName')
>INSERT PRODUCTPAGE VALUES(2,4,'ChildName')
>
>--select product and it's field
>/*select pro.prod_name, fie.field_name
>from product pro
>inner join productpage pg on pro.prod_id = pg.prod_id
>inner join fields fie on pg.field_name = fie.field_name*/
>
>***************
>END OF SCRIPT
>
>
>---------------------------------
>Do you Yahoo!?
>The New Yahoo! Search - Faster. Easier. Bingo.
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org