nullable foreign keys for all primary keys

Question

Post reply

nullable foreign keys for all primary keys

smita.patil

SSC Journeyman

Points: 77
More actions
February 20, 2010 at 10:18 am

#211306

Need some advise on database design.
What is the best practice to design a child table to specify value for all primary keys as wells as different value for specific values.
for e.g. master table has X, Y, Z
For X value is Val1 but for all others Val2.
My master table has many many records.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

I'd like to help, but the question isn't quite clear to me.

If you post some example data to demonstrate, it might help me understand it better.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

smita.patil SSC Journeyman Points: 77 More actions · Answer 2

Thanks Paul for your response

I have requirements where there is an amount which depends on several foreign keys from several primary keys.

for e.g. I have locations master table, machine types master table, users table and bands master table.

and all of the above tables are related by complex relationships in transactions table.

so it is like for A location , A user plays A machine type having A band.

any point of time the amount charged to user depends on all the above columns or a particular column or group of columns ,

like for all North locations the amount is 100 but only south location and B1 band 200 from start date to end date

or

all machine types 300 but west location, band B2 the amount is 300 from start date to end date.

so I have AmountSettings table where NULL means all i.e. All locations I will have LocationID as NULL.

now my transactions table is huge, but for each transaction I have to retrieve its amount from AmountSettings table.

I have query like:-

Transactions.StartDate BETWEEN AmountSettings.StartDate AND AmountSettings.EndDate

AND

AmountSettings.LocationID = Transactions.LocationID OR AmountSettings.LocationID is NULL

AND

AmountSettings.BandID =Transactions.BandID OR AmountSettings.BandID IS NULL

ANd so on for other foreign keys....

but this query is not good...

Paul White SSC Guru Points: 150467 More actions · Answer 3

Hello again,

It's a lot more difficult for people like me, who know nothing about your data, to understand what you mean, even with that longer explanation.

Maybe I'm just not very smart - but I still don't have a clear handle on the problem.

What would really help, and I appreciate that this is more work for you, would be a simplified example using a small number of simplified tables, with just enough sample data to make it clear what the problem is. The last thing would be to show an example SQL query based on the data you supply, and an idea of the sort of output you are expecting.

I often provide sample code as part of a solution, and it often looks a bit like the following, just to give you an idea of what I would like to see:

(This code does not relate to your question - it is just to help you see what is needed)

-- Table definition

DECLARE @data

TABLE (

[date] DATETIME NOT NULL PRIMARY KEY,

[close] DECIMAL(9,2) NOT NULL

);

-- Sample data

INSERT @data ([date], [close]) VALUES ('20100104', 10583.96);

INSERT @data ([date], [close]) VALUES ('20100105', 10572.02);

INSERT @data ([date], [close]) VALUES ('20100106', 10573.68);

INSERT @data ([date], [close]) VALUES ('20100107', 10606.86);

INSERT @data ([date], [close]) VALUES ('20100108', 10618.19);

INSERT @data ([date], [close]) VALUES ('20100111', 10663.99);

INSERT @data ([date], [close]) VALUES ('20100112', 10627.26);

INSERT @data ([date], [close]) VALUES ('20100113', 10680.77);

INSERT @data ([date], [close]) VALUES ('20100114', 10710.55);

INSERT @data ([date], [close]) VALUES ('20100115', 10609.65);

INSERT @data ([date], [close]) VALUES ('20100119', 10725.43);

INSERT @data ([date], [close]) VALUES ('20100120', 10603.15);

INSERT @data ([date], [close]) VALUES ('20100121', 10389.88);

INSERT @data ([date], [close]) VALUES ('20100122', 10172.98);

-- Sample query

WITH Ordered

AS (

-- Assume date order

SELECT rn = ROW_NUMBER() OVER (ORDER BY [date] ASC),

[date],

[close]

FROM @data

)

SELECT CurrentRow.[date],

CurrentRow.[close],

delta = (CurrentRow.[close] - PreviousRow.[close]) / PreviousRow.[close] * 100

FROM Ordered CurrentRow

OUTER

APPLY (

-- Previous row

SELECT [close]

FROM Ordered ORD

WHERE ORD.rn = CurrentRow.rn - 1

)

AS PreviousRow;

Expected output:

date close delta

======================= ======== ===============

2010-01-04 00:00:00.00010583.96NULL

2010-01-05 00:00:00.00010572.02-0.112812217700

2010-01-06 00:00:00.00010573.680.015701824200

2010-01-07 00:00:00.00010606.860.313798034300

2010-01-08 00:00:00.00010618.190.106817663200

2010-01-11 00:00:00.00010663.990.431335284000

2010-01-12 00:00:00.00010627.26-0.344430180400

2010-01-13 00:00:00.00010680.770.503516428500

2010-01-14 00:00:00.00010710.550.278818849200

2010-01-15 00:00:00.00010609.65-0.942061798800

2010-01-19 00:00:00.00010725.431.091270682800

2010-01-20 00:00:00.00010603.15-1.140094150000

2010-01-21 00:00:00.00010389.88-2.011383409600

2010-01-22 00:00:00.00010172.98-2.087608326500

Thanks!

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

smita.patil (2/21/2010)
I have query like:-
Transactions.StartDate BETWEEN AmountSettings.StartDate AND AmountSettings.EndDate
AND
AmountSettings.LocationID = Transactions.LocationID OR AmountSettings.LocationID is NULL
AND
AmountSettings.BandID =Transactions.BandID OR AmountSettings.BandID IS NULL
ANd so on for other foreign keys....
but this query is not good...

You have to group the OR's with parenthesis...

Transactions.StartDate BETWEEN AmountSettings.StartDate AND AmountSettings.EndDate

AND

[font="Arial Black"]([/font]AmountSettings.LocationID = Transactions.LocationID OR AmountSettings.LocationID is NULL[font="Arial Black"])[/font]

AND

[font="Arial Black"]([/font]AmountSettings.BandID =Transactions.BandID OR AmountSettings.BandID IS NULL[font="Arial Black"])[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 5

If Jeff's comment doesn't resolve all your problems, please do post the sample data, as I am still keen to take a look at this.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

smita.patil SSC Journeyman Points: 77 More actions · Answer 6

I already have parenthiesis on the query, but that query itself is wrong it gives multiple records for not null columns.