Help with Best practie and design

HI all,
I covert your input.
I am designing a claims database. I am looking at have a common claims
header table, that will keep all common thing to every claim, and then
seperate tables depending on the type/class of claim. So I could have a
motor, marine, asset for example. Reason being that there is information
that is not common between these classes, Eg. driver ID is only required for
motor, vessel name is only required for Marine etc. I don't want to have one
large claims table, with as many columns as there are different permutaions
of types of data for each claim class
My question is what is the best way to set this up. How wold I have a parent
common claims table, with say, a motor, marine, asset child table. How would
I relate these three table to the parent common claims table, because
obviously there will be rows in the parent that only have one corresponding
row in one of the other chiold tables, and never more than one corresponding
table.
Am I making this clear
THanks
RobertRobert
CREATE TABLE Parent_Claims
(
ParentId INT NOT NULL PRIMARY KEY,
ParentName VARCHAR(n) NOT NULL
....
)
CREATE TABLE Type_Claims
(
TypeId INT NOT NULL PRIMARY KEY,
TypeDescr VARCHAR(n) NOT NULL
...
)
CREATE TABLE Parent_Types
(
PT_ID INT NOT NULL PRIMARY KEY,
ParentID INT NOT NULL FOREIGN KEY REFERENCES Parent_Claims (ParentId ),
TypeId INT NOT NULL FOREIGN KEY REFERENCES Type_Claims(TypeId ),
)
Note: I don't know your business requirements hence tried to give a common
solution
"Robert Bravery" <me@.u.com> wrote in message
news:eOj7Ub6NGHA.2916@.tk2msftngp13.phx.gbl...
> HI all,
> I covert your input.
> I am designing a claims database. I am looking at have a common claims
> header table, that will keep all common thing to every claim, and then
> seperate tables depending on the type/class of claim. So I could have a
> motor, marine, asset for example. Reason being that there is information
> that is not common between these classes, Eg. driver ID is only required
> for
> motor, vessel name is only required for Marine etc. I don't want to have
> one
> large claims table, with as many columns as there are different
> permutaions
> of types of data for each claim class
> My question is what is the best way to set this up. How wold I have a
> parent
> common claims table, with say, a motor, marine, asset child table. How
> would
> I relate these three table to the parent common claims table, because
> obviously there will be rows in the parent that only have one
> corresponding
> row in one of the other chiold tables, and never more than one
> corresponding
> table.
> Am I making this clear
> THanks
> Robert
>|||Hi Uri,
Thanks
Yes I got that. But now added those two extra tables, that are not common,
how would I relate them to the commaon parent clain;
CREATE TABLE [dbo].[Marine] (
[MarineID] [int] NOT NULL ,
[VesselName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Motor] (
[MotorID] [int] NOT NULL ,
[DriverID] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Parent_Claims] (
[ParentId] [int] NOT NULL ,
[ParentName] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Type_Claims] (
[TypeId] [int] NOT NULL ,
[TypeDescr] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Parent_Types] (
[PT_ID] [int] NOT NULL ,
[ParentID] [int] NOT NULL ,
[TypeId] [int] NOT NULL
) ON [PRIMARY]
GO
THanks
Robert
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#H#OXu6NGHA.208@.tk2msftngp13.phx.gbl...
> Robert
>
> CREATE TABLE Parent_Claims
> (
> ParentId INT NOT NULL PRIMARY KEY,
> ParentName VARCHAR(n) NOT NULL
> .....
> )
> CREATE TABLE Type_Claims
> (
> TypeId INT NOT NULL PRIMARY KEY,
> TypeDescr VARCHAR(n) NOT NULL
> ....
> )
> CREATE TABLE Parent_Types
> (
> PT_ID INT NOT NULL PRIMARY KEY,
> ParentID INT NOT NULL FOREIGN KEY REFERENCES Parent_Claims (ParentId ),
> TypeId INT NOT NULL FOREIGN KEY REFERENCES Type_Claims(TypeId ),
> )
>
> Note: I don't know your business requirements hence tried to give a
common
> solution
>
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:eOj7Ub6NGHA.2916@.tk2msftngp13.phx.gbl...
>|||> My question is what is the best way to set this up. How wold I have a
> parent
> common claims table, with say, a motor, marine, asset child table. How
> would
> I relate these three table to the parent common claims table, because
> obviously there will be rows in the parent that only have one
> corresponding
> row in one of the other chiold tables, and never more than one
> corresponding
> table.
> Am I making this clear
create table claim (
claim_id int not null,
...
constraint [pk_claim] primary key nonclustered
(claim_id)
)
create table motor_claim (
claim_id not null,
...
constraint [pk_motor_claim] primary key nonclustered
(claim_id)
)
alter table motor_claim
add constraint [fk_blah] foreign key (claim_id)
references dbo.claim (claim_id)
This supports a 1-to-1 relationship between claim and any particular child
type table. It does NOT limit a claim to having a single row in a single
child table. For that, you would need another constrainting device (e.g.,
trigger, indexed view).|||Look up some of my old postings about "faking" class hierarchies in
SQL. Then read why this is a horrible approach to RDBMS.
Also, there is no such data element as a "type_id". An attribute can
be a *particular* kind of identifier or a *particular* kind of type.
It cannot be both and it cannot be so vague. Think about it for a
second.|||--CELKO-- wrote:
> Look up some of my old postings about "faking" class hierarchies in
> SQL. Then read why this is a horrible approach to RDBMS.
Do you mean your "CREATE TABLE Vehicles" postings? Where can I read
your critique of this approach?
Thanks,
Jamie.

Help with a query

Guys-

Any help with this query is greatly appreciated...

DECLARE @.input TABLE
(NodeId VARCHAR(10),
IsChecked CHAR(1))

DECLARE @.Actual TABLE
(AndSetId INT,
NodeId VARCHAR(10),
IsChecked CHAR(1))

INSERT INTO @.Input VALUES ('a', 'T')
INSERT INTO @.Input VALUES ('b', 'T')
INSERT INTO @.Input VALUES ('c', 'F')
INSERT INTO @.Input VALUES ('d', 'F')
INSERT INTO @.Input VALUES ('e', 'T')

INSERT INTO @.Actual VALUES (1, 'a', 'T')
INSERT INTO @.Actual VALUES (1, 'b', 'T')
INSERT INTO @.Actual VALUES (1, 'c', 'F')

INSERT INTO @.Actual VALUES (2, 'c', 'F')
INSERT INTO @.Actual VALUES (2, 'd', 'F')

INSERT INTO @.Actual VALUES (3, 'd', 'F')

INSERT INTO @.Actual VALUES (5, 'd', 'F')
INSERT INTO @.Actual VALUES (5, 'e', 'F')

INSERT INTO @.Actual VALUES (6, 'f', 'F')

INSERT INTO @.Actual VALUES (7, 'g', 'F')

-- We should get back 1, 2, 3, 4. We should not get back 5, 6, 7Did your instructor include the rules (the logic) behind this, or only give you the two inputs and the output that they expected? Have you recently covered anything like this in class?

-PatP|||Pat-

My apologies for not elaborating enough, what I was looking for.

Get all AndSetIds from @.Actual where

if NodeId is present in @.input then the isChecked should match.
eg: AndSetId 1
It has 3 records. And the records match with the records
in Input.
So 1 should be returned.

Where as let us look at AndSet 5.
It has NodeId 'e' as 'F' where as the input has 'e' as 'T'
So 5 should not be returned.

INSERT INTO @.Input VALUES ('a', 'T')
INSERT INTO @.Input VALUES ('b', 'T')
INSERT INTO @.Input VALUES ('c', 'F')
INSERT INTO @.Input VALUES ('d', 'F')
INSERT INTO @.Input VALUES ('e', 'T')

INSERT INTO @.Actual VALUES (1, 'a', 'T')
INSERT INTO @.Actual VALUES (1, 'b', 'T')
INSERT INTO @.Actual VALUES (1, 'c', 'F')

INSERT INTO @.Actual VALUES (2, 'c', 'F')
INSERT INTO @.Actual VALUES (2, 'd', 'F')

INSERT INTO @.Actual VALUES (3, 'd', 'F')

INSERT INTO @.Actual VALUES (5, 'd', 'F')
INSERT INTO @.Actual VALUES (5, 'e', 'F')

INSERT INTO @.Actual VALUES (6, 'f', 'F')

INSERT INTO @.Actual VALUES (7, 'g', 'F')|||Never mind. I think I have a solution. Thanks for your time Pat.sql

help Stored proc

Hi,
Could any one please help me in creating sp.
It should accept one input parameter. when you pass value 1 to this parameter it should show all odd numbers from 1 to 100 and when you pass value 2 it should show all even numbers from 1 to 100.
Thanks in advance.
-ssTry this:

create procedure p_odd_even(@.i int)
as
select number -1 + @.i from master..spt_values where type = 'P' and number % 2 = 1 and number <= 100|||Very clever trick - what is the purpose of this table in SQL|||too clever

you would need DISTINCT in there, vaxman, and also restrict number to between 0 and 100, not just less than or equal to 100

the use of master..spt_values is a hack

better to declare an integers table, because it will come in handy in so many other queries|||Not sure what spt_values is for. I have seen it used for things like this. In examining the table, it looks like type 'P' does return distinct integers, but as Rudy says, much better to have your own table. I create one like this:

select top 8000 id = identity(int,1,1) into Numbers from sysobjects s1, sysobjects s2, sysobjects s3

(8000 because I usually use it for parsing varchar strings but change for your needs)

Or you can just generate your 50 numbers on the fly. For 50 numbers this is probably more efficient (no I/O and 50 numbers is small enough not to generate a work table) but for larger counts a real table is better because statistics will be kept for it and indexes used.

create procedure p_odd_even(@.i int)
as

select (a0.id + a1.id) + @.i id
FROM
(select 0 id union select 2 union select 4 union select 6 union select 8 ) a0,
(select 0 id union select 10 union select 20 union select 30 union select 40
union select 50 union select 60 union select 70 union select 80 union select 90) a1
order by 1|||You could always use:CREATE PROCEDURE pSSkris
@.arg INT = 1
AS

SELECT n
FROM (SELECT 1 + 10 * tens + ones AS n
FROM (SELECT 0 AS ones UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS a
CROSS JOIN (SELECT 0 AS tens UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS b) AS c
WHERE n % 2 = 2 - @.arg
ORDER BY n

RETURN
GO-PatP|||uh Pat, didn't I say that? (but you only need 50 numbers...)|||uh Pat, didn't I say that? (but you only need 50 numbers...)Sort of... You relied on another table outside of the problem definition, but my suggestion was self contained. I suspect that mine will be easier to explain too, but that's a relative kind of thing.

-PatP|||I think you missed this part:

create procedure p_odd_even(@.i int)
as
select (a0.id + a1.id) + @.i id
FROM
(select 0 id union select 2 union select 4 union select 6 union select 8 ) a0,
(select 0 id union select 10 union select 20 union select 30 union select 40
union select 50 union select 60 union select 70 union select 80 union select 90) a1
order by 1|||Sorry, I was only looking at your first posting in my previous comment.

-PatP|||As an interesting wrinkle, you could also use:SELECT n
FROM (SELECT d0.b + d1.b + d2.b + d3.b + d4.b + d5.b + d6.b AS n
FROM (SELECT 0 AS b UNION SELECT 1) AS d0
CROSS JOIN (SELECT 0 AS b UNION SELECT 2) AS d1
CROSS JOIN (SELECT 0 AS b UNION SELECT 4) AS d2
CROSS JOIN (SELECT 0 AS b UNION SELECT 8) AS d3
CROSS JOIN (SELECT 0 AS b UNION SELECT 16) AS d4
CROSS JOIN (SELECT 0 AS b UNION SELECT 32) AS d5
CROSS JOIN (SELECT 0 AS b UNION SELECT 64) AS d6
) AS z
WHERE n BETWEEN 1 AND 100
ORDER BY n-PatP|||As an interesting wrinkle, you could also use:CREATE PROCEDURE pSSkris2
@.arg INT = 1
AS

SELECT n
FROM (SELECT d0.b + d1.b + d2.b + d3.b + d4.b + d5.b + d6.b AS n
FROM (SELECT 2 - @.arg AS b) AS d0
CROSS JOIN (SELECT 0 AS b UNION SELECT 2) AS d1
CROSS JOIN (SELECT 0 AS b UNION SELECT 4) AS d2
CROSS JOIN (SELECT 0 AS b UNION SELECT 8) AS d3
CROSS JOIN (SELECT 0 AS b UNION SELECT 16) AS d4
CROSS JOIN (SELECT 0 AS b UNION SELECT 32) AS d5
CROSS JOIN (SELECT 0 AS b UNION SELECT 64) AS d6
) AS z
WHERE n BETWEEN 1 AND 100
ORDER BY n

RETURN-PatP|||I wonder if you'll get an "A"...|||Who wants to explain it? Without an explanation, I'd expect that the code is worthless.

-PatP|||if it's for a school assignment, i would stringly suggest to sskris to submit the solution in post #2

that'll get an A+|||I would expect that any of the solutions that we've offered will get a trip to the Dean's office and an opportunity to use this experience in their upcomming ethics class (whether it was part of the curriculum before now or not). These solutions may help them think about the answer they want to give, but I can't imagine any of them being "safe" to turn in as they are.

-PatP|||Thanks to all of you..
I really appreciate that.
Kris