Unexpected Results - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Unexpected Results

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-08 : 10:12:36

What am I missing, i have the following view called SnapsRaw:

SELECT
dbo.RS_Customers.BankID,
dbo.RS_Customers.BranchID,
dbo.RS_BranchCode.BranchState,
dbo.RS_Customers.AccountID,
dbo.RS_Customers.AccountName,
dbo.RS_Customers.InvestAuthID,
dbo.RS_Customers.RelationshipID,
dbo.RS_Relationship.Company,
dbo.RS_CustomerValue.ValueAmount AS [Account Market Value],
dbo.RS_Customers.CategoryID AS Category,
dbo.RS_Category.Description AS CategoryDescription,
dbo.RS_Customers.StyleInvestID AS [Sales Officer],
dbo.RS_Customers.SicID,
dbo.RS_SICSub_Group.Description AS SicDescription,
dbo.RS_Customers.Zipcode,
dbo.RS_Customers.SectorID,
dbo.RS_BusinessSector.Description AS SectorDescription,
dbo.RS_Customers.MonthEndID,
dbo.RS_Month.MonthEnd
FROM
dbo.RS_CustomerValue INNER JOIN
dbo.RS_Month ON dbo.RS_CustomerValue.MonthEndID = dbo.RS_Month.MonthEndID INNER JOIN
dbo.RS_Customers INNER JOIN
dbo.RS_BranchCode ON dbo.RS_Customers.BranchID = dbo.RS_BranchCode.BranchID INNER JOIN
dbo.RS_Relationship ON dbo.RS_Customers.RelationshipID = dbo.RS_Relationship.RelationshipId ON
dbo.RS_CustomerValue.AccountID = dbo.RS_Customers.AccountID AND
dbo.RS_Month.MonthEndID = dbo.RS_Customers.MonthEndID LEFT OUTER JOIN
dbo.RS_Category ON dbo.RS_Customers.CategoryID = dbo.RS_Category.CategoryID LEFT OUTER JOIN
dbo.RS_BusinessSector ON dbo.RS_Customers.SectorID = dbo.RS_BusinessSector.SectorID LEFT OUTER JOIN
dbo.RS_SICSub_Group ON dbo.RS_Customers.SicID = dbo.RS_SICSub_Group.SicsubID

i cant query on an account that doesnt have two records in RS_CustomerValue and two records in RS_Customers, if the account has two records in RS_CustomerValue, and two records in RS_Customers I am fine, same goes for one record in each, but when I have a situation with one record in RS_CustomerValue and two records in RS_Customers i dont bring back anything. i hope i explained this ok, just for clarification, the one customers table stores all the customer information month/month, and the customervalue table stores the customers account value month/month, the reason we might have a case where we have two records in customers and one record in customervalue would be because the account wasnt funded the first month, but was created

when i try to :

select * from SnapsRaw where AccountID like '96K202AC1'
i get nothing

when i try to :

select * from RS_CustomerValue where AccountID = '96K202AC1'

i get

96K202AC1 2 82392.4200 .0000 .0000

when i try to :

select * from RS_Customers where AccountID = '96K202AC1'

i get

96K202AC1 NULL DEF 401K-JOMEL NICHL 98 1
96K202AC1 NULL DEF 401K-JOMEL NICHL 98 2

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 11:08:32

You need to give the table layout for the three tables Customers, CustomerValue and MonthEnd. The problem is in the joins between them, but you probably also have a bad design.

You should only have one row per customer, and then have multiple month ends per customer, something like

Customer - AccountID
MonthEnd - MonthEndId, CustomerAccountID
CustomerValue - MonthEndId

Or even just do away with the CustomerValue alttogether and put the values into the MonthEnd table?

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-08 : 11:25:51

I appreciate the advice, my question is, what if I have information in the customer table that could change on a monthly basis that I want to be able to query on months later. Example, RS_CUSTOMERS table looks like this:
RS_CUSTOMERS
AccountID ----PRIMARY KEY
AdminID ----FOREIGN KEY
AccountName
BankID ---FOREIGN KEY, PRIMARY KEY
BranchID ----FOREIGN KEY
CategoryID ---FOREIGN KEY
InvestAuthID ----FOREIGN KEY
MonthEndID -----FOREIGN KEY, PRIMARY KEY
OfficerID ----FOREIGN KEY
OpenDate
RelationshipID --FOREIGN KEY
SectorID --_FOREIGN KEY
SicID --FOREIGN KEY
StyleInvestID --FOREIGN KEY
Zipcode

I want to keep month to month history, and some of those foreign keys fields could change month/month. I am very new to the database design world, and appreciate all the great advice you all have to offer me.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 11:31:02

Then all those columns would move to the monthly table. Right now you have a Customers table that is also customer monthly record table, you never want to have that. The Customers table must have one row per customer. Then you want to have a monthly customer table with one row per customer per month that has a foreign key relating it to the customer table.

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-08 : 11:36:42

So basically I would have the following:

Customer Table:
AccountName
Account# - primarykey
opendate
zipcode

Customer Monthly Table:
Account# -Foreignkey
Adminid -foreignkey
and so on...
...
..
basically all the foreigns keys that i had in the customer table??? would it matter if i keep the account values seperate still? is there a benefit to doing that

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 11:49:45

The rule in database normalization (which is what you're doing here

) is that every non-primary key column must be completely dependent on the primary key, and only dependent on the primary key column.

It sounds like the monthly values are completely dependent on the monthly primary key, so there's no reason to put them in a separate table.

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-08 : 12:06:42

I appreciate the great advice. Does my logic in having a RS_Month table where I define two colums:

Monthid
Monthend

example: monthid:1 monthend:July 2006 monthid:2 monthend: august 2006

than i reference the monthid in the customer monthly table? Or is there a better way

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 12:32:49

That one is open to debate - and is hotly debated here and on other sites all the time.

In your case RS_Month may not be necessary but it is not a bad idea to have it. It becomes more necssary as you want to start adding other ways of looking at each month, for example you might have a Quarter column in that table. The reason it's not always necessary is that you can usually calculate those other values from the date itself. What I would suggest is that you break up the monthend column into month and year, so the table becomes

Monthid
Month
Year

example:
monthid:1 month:July year: 2006
monthid:2 month:August year: 2006

Another rule of normalization is that you don't want to store more than one piece of data in a column.

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-08 : 12:41:43

is it bad than that you always have to know what ID corresponds to a given month/year? or is that where reporting services or some front end would come in

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-08 : 12:42:35

one more quick thing, what datatype do you recommend you apply to month/year?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 13:21:57

quote:
Originally posted by duhaas

is it bad than that you always have to know what ID corresponds to a given month/year? or is that where reporting services or some front end would come in

Well, that's where you need to think about whether you want to have the dates as a separate table, if you rather put the date in the monthly records and then just use calculations for getting the month or the year you wouldn't have to figure out what those cryptic IDs are. For most data you can't just calculate the other values so you have to have a separate table.

Another option is to make the date table ID a char(8) column instead of an integer and then use the ISO date format of yyyymmdd as the ID and for example, always use the first day of the month as the id, so your data becomes

example:
monthid:20060701 month:July year: 2006
monthid:20060801 month:August year: 2006

Make sure you still define the ID column as the primary key, but it doesn't have to be an int.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 13:25:10

quote:
Originally posted by duhaas

one more quick thing, what datatype do you recommend you apply to month/year?

Nothing special, probably varchar(8) for the month and a numeric for the year (then its easy to calculate differences between years if necessary). You can always cast/convert a numeric to char or varchar when using it as a string.

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-08 : 13:33:47

Excellant, thanks, I am just in the process of redoing these table relationships, and the one thing I just ran into was the following:

Table: RS_CustomerID has primary key of AccountID & AccountBankID since we could have the same account number defined in two different banks.

Table: RS_CustomerValue has a primary key of AccountID & MonthendID

I cant create a relationship now between the two since the combination of the two keys are different, how do I get around this? I keep getting the CustomerID does not match an existing primary key or unique constraint

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 14:53:43

The foreign key in CustomerValue goes straight to the MonthEnd table now, it doesn't go to the Customer table at all - again I'm not sure you need the CustomerValue table at all - it just has values for one Customer for one month right? In that case put the values right in the MonthEnd table.

If you do keep the CustomerValue table you'll always join it to MonthEnd to get the Customer id.

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-08 : 15:20:52

OK, sorry, just a little confused, I have the following three tables:

TABLE1 (RS_CUSTOMERID) - this is where i stored information about my customer
(Accountid [PK]) and (Bankid [PK] and [FK]) are primary keys
TABLE2 (RS_CUSTOMERVALUE) - this is where i will store the historical information for each customer, including marketvalue
(AccountId PK/FK) (MonthID FK) are the primary keys
TABLE3 (RS_MONTH) - this is where i define each monthend date
(Monthendid PK)

How do i get the customervalue and customerid to relation on accountid? Sorry if you are getting frustrated, I am almost there.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 15:55:06

A foreign key always matches to a whole primary key somewhere. So it will look like this (I have repeated column names where necessary, just referring to the same column name in its different roles as PK and/or FK)

TABLE1 (RS_CUSTOMERID) -
(AccountId, BankId [PK]); (Bankid [FK])
TABLE2 (RS_CUSTOMERVALUE) -
(AccountId, MonthId [PK]); (AccountId, BankId [FK]); (MonthendID [FK])
TABLE3 (RS_MONTH) -
(Monthendid [PK])

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-08 : 16:06:25

If I dont make the combination of Accountid/BankID a primary key in the customerid table, how can i add the same account but with different bankid's. Shouldnt both accountid and bankid be primary keys, as well as bankid being a foreignkey?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 16:13:45

Correct - when I said
(AccountId, BankId [PK]); (Bankid [FK])
I meant that AccountId, BankId together are the primary key and BankId is a foreign key.

Note that you should actually put them the other way round in the primary key, so
BankId, AccountId
(less selective column first) because that will create a better index for this table.

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-08 : 16:19:14

ok excellant, now back to my first question, you are being a saint. now when i try to make the accountid which is already a primary key a foreign key in the RS_CUSTOMERVALUE table thats when i get the message CustomerID does not match an existing primary key or unique constraint. Remember my two primary keys in the customervalue table are Accountid and Monthendid. I want to make accountid a foriegn key as well. also the second primary key monthendid is not a part of my customerid table

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 17:35:21

Just to clarify - a table can only have one primary key. The primary key can be composed of more than one column, but there can only be one primary key.

Now - your foreign key in CustomerValue is not to AccountId, because AccountId is not a primary key in Customer. The primary key in Customer is (BankId, AccountId), so the foreign key must be (BankId, AccountId).

That brings up an interesting point - many people (myself included) like to create a special internal primary key for each table instead of using existing columns. So for example, in Customer you could create an identity column like CustomerId and make it the primary key. You still have a unique index on (BankId, AccountId) but you don't make it the primary key. Now instead of having to have AccountId and BankId in the CustomerValue table, you'd just have CustomerId there as the foreign key.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources