Unique constraint but data values have changed!

Question

Unique constraint but data values have changed!

david.goodmanson

SSC Rookie

Points: 45
More actions
January 6, 2009 at 12:47 am

#217178

I am developing a database based on the Australian Stock Exchange (ASX). There is only one table at the moment (dbo.ASX_Data). It has 8 fields ("TradeID" Identity PK, "ASX_Code" Chr(10), "TradeDt" datetime, "Open" real, "High" real, "Low" real, "Close" real, "Volume" int). The table has 3 million rows. The table contains daily trading data from the ASX and grows by approx 2,500 rows per business day. I assumed (incorrectly) that there was one ASX_Code for one day's trade. Therefore I created a Unique Constraint on ASX_Code and TradeDt. This is, in one sense, still true however the fact is that an ASX can change over time.
This is my dilemna. :crazy: Further research has shown me that an ASX_Code can change where one company takes over another, when it is de-listed, when there is a merger. Mergers are interesting because there are effectively two data trails which merge into one data trail at a point. I have also now found out that the ASX publish lists of these events, (though not all are easily downloaded). Companies can also be de-registered, go insolvent and there are probably others. Sometimes companies change their code where the structure of it has changed - no merger or acquisition. I also suspect that the ASX can reissue ASX_Codes. I naively overlooked all as I enthusiastically jumped right in. So, I'm guilty as charged! 🙂
Anyway, one tip that I have received (although very generalised), Is to create an ASX_Master table with a surrogate key, as well as the Effective Date of the change in ASX_Code. I do plan to create a second table which would also include the full company name. This may be the answer, although I'm not sure how to associate the Old Code and the NewCode, in one row? This is the point where I start to run out of answers. I am hoping that there may be people here who have some sage advice on this problem. Do I overwrite the old ASX_Code and change history? I would really appreciate some specifics on how exactly I can overcome this problem.
This is my first post to this group. I'm not sure if I get an email if someone replies to this, but my email is:
Much appreciated.
Thanks
David
Albury NSW Australia

Viewing 9 posts - 1 through 8 (of 8 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736232 More actions · Answer 1

David,

You'll get a reply by default to this when people post, so no need to disclose your email. I'll edit it out so not bots grab it.

Tracking stock codes is indeed a problematic thing to do. history does change and so you have to account for that. I used to do some of this, though less complex than you.

For us, the history of the symbol was kept in a separate table along with an effective date.

We used a surrogate key in the primary table along with the current symbol for that row. This allowed us to join with the first table to get the display symbol across time, but you want to be sure you know the rules for display. Ticker changes usually just get noted in the client display/report. Merges sometimes mean that the history isn't really valid past a certain date for some reports.

I'll ping a few people I know and see if they can shed more light on this. My info is well over a decade old.

GSquared SSC Guru Points: 260824 More actions · Answer 2

You could also set up a hierarchy table that would include all codes that fall under any other code. That would give you a way to quickly determine which codes were part of any other code. If you include effective dates in that hierarchy, it could be your history as well.

create table ASXCodes (

ASXCode char(10) not null,

ParentASXCode char(10) null,

StartDate datetime not null default(getdate()),

EndDate datetime null)

Then query that, using a standard recursive CTE (assuming your database is SQL 2005 or later), and use the Start and End Date columns in the Where clause of your CTE, and you'd have the ability to hook that to every transaction that was applicable, without having to change transaction history.

Is that clear enough or did I just throw mud on it?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SQLRockstar SSCertifiable Points: 7014 More actions · Answer 3

hi david.

if i understand your post, you are struggling with a way to associate the old and new code within the same row in a tbale, correct?

if so, would it suffice to use a column named "previous_code"? that way, as codes change you would know the prvious code, and you could put together a self-join to get the entire hierarchy over time.

does that make sense?

----------------------
https://thomaslarock.com

david.goodmanson SSC Rookie Points: 45 More actions · Answer 4

Thanks Steve,

In fact thanks to all those who have replied. (There's not a "reply to all" option). I fear there is no quick fix to this. As you say there is a need for another table. The mechanics of that table(s) is not clear to me.

As an example, a company largely owned by a famous Australian Kerry Packer, changed its ASX_Code in Dec 2007. It had always been known as PBL, but Kerry's son Jamie sold off some parts of PBL, for whatever reason as from that date in Dec 07 the company is now CMJ. No merger, no takeover, just a re-structure. So I have a trail of data for PBL, which stops dead and later (like late last year) I discover the ASX_Code has changed. Not sure how I maintain a continuity for this company.

I suspect I'm going to be manually updating data for quite some time.

Much appreciated for your suggestion and I will look closely at the operation of this second table.

Regards

Dave.

david.goodmanson SSC Rookie Points: 45 More actions · Answer 5

Thanks GSquared,

I'm using SQL Server 2000.

I don't know what a standard recursive CTE is? That's why I'm posting in the Newbies group.

I agree that I need to get a second table going which deals with ASX_Codes and Effective dates. As I explained to Steve, in Australia a well known company changed ASX_Codes from PBL to CMJ. Due to a restructure of the company. Now I have a trail of PBL data which stops, and, unbeknowns to me at the time then continues life as CMJ.

I can't emphasise what a monumental f*%? up this has been. It is truly a nightmare. Obviously Managing these changes is the key to it. I'll continue to try the second table, and will use your code for creating that table.

Thanks

Dave

david.goodmanson SSC Rookie Points: 45 More actions · Answer 6

Hi Thomas,

Yes, the previous code suggestions sounds feasible. That might be the left field kind of suggestion I need. I definitely want to avoid trawling manually through 3 million rows to edit some of these company codes.

Thanks

Dave

Christo Wolmarans Ten Centuries Points: 1170 More actions · Answer 7

Hi Dave

I'd suggest that you create a secondary table that keeps track of the changes in ASX_code per company over time. The definition might look something like this:

CREATE TABLE Companies (

PKID INT IDENTITY(1,1) PRIMARY KEY, --Primary Key of the table

CompanyID INT, --The Primary Key of the Company, this will link back to the Main company table

ASX_Code INT, --I am assuming the type

StartDate DATETIME, --The date that the company started using this ASX_Code

EndDate DATETIME

)

You will then also have to add a reference ID in your current table that is unique for each company (CompanyID)

Hope this leads you on the right path.

Regards

Christo

david.goodmanson SSC Rookie Points: 45 More actions · Answer 8

Thanks,

That looks good, I think I now have some answers to go on with.

Regards

Dave