Dependant Data Table

I am new to SQL Server. I am using Sql Server2000. I am designing and
application that offers services to user. I have a webapplication with a no
of forms. In form1 i am asking the user to select all those services he
wants. Once he selects services I take him to the other forms where he
customizes services as per his requirements.
I am storing the selected services in table1 that has following columns
1)CaseID (Primary Key)
2)Service1 Boolean
3)Service2 Boolean
Now I want that if value of Service1 is yes/true then the customized details
for this service shall be stored in Table2 that has following Fileds
1)CaseID
2)Preference1
3)preference2
Similarly for service2 I have another table - table3 becoz for each service
i have diffent attributes to be customized. I want to store the values in the
tables associated with the services if and only if service field is yes in
Table1.
Also I want to create a view that would State all the services selected in a
case and also retrieve the customized preferences from all the tables realted
to selected services. How shall i do that ? I cannot create a static view
with all the services since different users may select different services.
Please Help. I am new to SQL Server development.
Thank You.
Saket Mundra
Your design of Table1 needs improving. Something like this maybe:
CREATE TABLE Cases /* Table1 in your example */ (case_id INTEGER PRIMARY KEY
/* The presence of absence of rows in the services tables indicates what
services are chosen so the repeating group of booleans is redundant */)
CREATE TABLE Services1 /* Table2 in your example */ (case_id INTEGER PRIMARY
KEY REFERENCES Cases (case_id), service_no INTEGER DEFAULT (1) NOT NULL
CHECK (service_no = 1) /*, some other cols here ... */)
CREATE TABLE Services2 (case_id INTEGER PRIMARY KEY, service_no INTEGER
DEFAULT (2) NOT NULL CHECK (service_no = 2), FOREIGN KEY (case_id)
REFERENCES Cases (case_id), foo_col ... etc)
CREATE TABLE Services3 (case_id INTEGER PRIMARY KEY, service_no INTEGER
DEFAULT (3) NOT NULL CHECK (service_no = 3), FOREIGN KEY (case_id)
REFERENCES Cases (case_id), bar_col ... etc)
The view you require is just a left join from Cases to each of the Service
tabes.
David Portas
SQL Server MVP
|||Thank you david for your response. I regret for posting the query in
different groups.
I have looked into my table design and now its something like this
Table Cases [CaseID(primary Key), Case_details]
This table is used for case creation
Table Case_Service [CaseID (Foreign Key), ServiceID].
this table is used to store all the services selected by customer
Table Service1_details [CaseID (foreign Key), Pref1, Pref2]
Table Service2_details [CaseID (foreign Key), Pref1, Pref2]
These tables are used to store deatils of services if selected.
As per your advice I have created a view using left join and it works.But it
returns null values for the services not selected. I am curious to know if
its possible to create a view dynamically on the fly in which only fileds
from those Service_details tables are selected for which service ID is
present in Case_service Table so that for each customer a view is created
dynamically as per his selection of services. If yes I would request you to
please guide me.
Thank you very much for your help once again.
Saket Mundra
"David Portas" wrote:

> Your design of Table1 needs improving. Something like this maybe:
> CREATE TABLE Cases /* Table1 in your example */ (case_id INTEGER PRIMARY KEY
> /* The presence of absence of rows in the services tables indicates what
> services are chosen so the repeating group of booleans is redundant */)
> CREATE TABLE Services1 /* Table2 in your example */ (case_id INTEGER PRIMARY
> KEY REFERENCES Cases (case_id), service_no INTEGER DEFAULT (1) NOT NULL
> CHECK (service_no = 1) /*, some other cols here ... */)
> CREATE TABLE Services2 (case_id INTEGER PRIMARY KEY, service_no INTEGER
> DEFAULT (2) NOT NULL CHECK (service_no = 2), FOREIGN KEY (case_id)
> REFERENCES Cases (case_id), foo_col ... etc)
> CREATE TABLE Services3 (case_id INTEGER PRIMARY KEY, service_no INTEGER
> DEFAULT (3) NOT NULL CHECK (service_no = 3), FOREIGN KEY (case_id)
> REFERENCES Cases (case_id), bar_col ... etc)
> The view you require is just a left join from Cases to each of the Service
> tabes.
> --
> David Portas
> SQL Server MVP
> --
>
>
|||A static query has static metadata. In other words the number, name and
type of its columns is fixed. You could create an SP that optionally
returns different sets of columns (using IF statements for exaple) or
you could use dynamic SQL. Dynamic SQL is generally something to be
avoided in production code though. I would tend towards the idea that
you should hide the redundant columns in your presentation tier rather
than worry too much about handling it in the database.
Regarding your revised design. I recommend you add the Serviceid to
each service table (properly constrained with a CHECK constraint in
each case) and make the foreign key reference as follows: (caseid,
serviceid) REFERENCES Case_Service (caseid, serviceid). Otherwise you
could potentially have an anomaly where a case has details for a
service that doesn't exist in Case_Service. That anomaly wasn't
possible in my model because I didn't have the extra table but if you
feel you need that table then I think the compound foreign key is
essential.
David Portas
SQL Server MVP
|||Thanx David. Your suggestions helped me greatly.
If you could please elaborate on how to create an SP that optionally
returns different sets of columns or how to use dynamic SQL. I am new to Sql
server development and would be grateful if you cud help me out with this.
Lets assume that a customer selects service one and discards service two.
Now if you could please tell me how to create a view dynamically that checks
which service the customer has selected and then includes fields from
respective service_details tables. Kindly Help.
Thank You very much.
Saket Mundra
"David Portas" wrote:

> A static query has static metadata. In other words the number, name and
> type of its columns is fixed. You could create an SP that optionally
> returns different sets of columns (using IF statements for exaple) or
> you could use dynamic SQL. Dynamic SQL is generally something to be
> avoided in production code though. I would tend towards the idea that
> you should hide the redundant columns in your presentation tier rather
> than worry too much about handling it in the database.
> Regarding your revised design. I recommend you add the Serviceid to
> each service table (properly constrained with a CHECK constraint in
> each case) and make the foreign key reference as follows: (caseid,
> serviceid) REFERENCES Case_Service (caseid, serviceid). Otherwise you
> could potentially have an anomaly where a case has details for a
> service that doesn't exist in Case_Service. That anomaly wasn't
> possible in my model because I didn't have the extra table but if you
> feel you need that table then I think the compound foreign key is
> essential.
> --
> David Portas
> SQL Server MVP
> --
>
|||Examples:
CREATE PROC usp_service_details_static
(@.caseid, @.serviceid)
AS
IF @.serviceid = 1
SELECT /* ... column list */
FROM service1_details
WHERE caseid = @.caseid ;
IF @.serviceid = 2
SELECT /* ... column list */
FROM service2_details
WHERE caseid = @.caseid ;
IF @.serviceid = 3
SELECT /* ... column list */
FROM service3_details
WHERE caseid = @.caseid ;
RETURN
GO
CREATE PROC usp_service_details_dynamic
(@.caseid, @.serviceid)
AS
DECLARE @.sql VARCHAR(8000)
SET @.sql =
'SELECT * FROM '+
CASE @.serviceid
WHEN 1 THEN 'service1_details'
WHEN 2 THEN 'service2_details'
WHEN 3 THEN 'service3_details'
END
+' WHERE caseid = @.caseid ;'
EXEC (@.sql)
RETURN
GO
Before you attempt any dynamic code read the following article to make
sure you understand the implications:
http://www.sommarskog.se/dynamic_sql.html
David Portas
SQL Server MVP
|||Thank You for your help and time David. you really helped me a great deal.
Saket Mundra
"David Portas" wrote:

> Examples:
> CREATE PROC usp_service_details_static
> (@.caseid, @.serviceid)
> AS
> IF @.serviceid = 1
> SELECT /* ... column list */
> FROM service1_details
> WHERE caseid = @.caseid ;
> IF @.serviceid = 2
> SELECT /* ... column list */
> FROM service2_details
> WHERE caseid = @.caseid ;
> IF @.serviceid = 3
> SELECT /* ... column list */
> FROM service3_details
> WHERE caseid = @.caseid ;
> RETURN
> GO
>
> CREATE PROC usp_service_details_dynamic
> (@.caseid, @.serviceid)
> AS
> DECLARE @.sql VARCHAR(8000)
> SET @.sql =
> 'SELECT * FROM '+
> CASE @.serviceid
> WHEN 1 THEN 'service1_details'
> WHEN 2 THEN 'service2_details'
> WHEN 3 THEN 'service3_details'
> END
> +' WHERE caseid = @.caseid ;'
> EXEC (@.sql)
> RETURN
> GO
> Before you attempt any dynamic code read the following article to make
> sure you understand the implications:
> http://www.sommarskog.se/dynamic_sql.html
> --
> David Portas
> SQL Server MVP
> --
>