The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Online trading database

If you had to implement an online trading system would you use the stock symbol or the id to link the tables (stocks, orders, transactions, portfolio, etc)? Note that the symbol is very short (3 to 5 chars - maximum 10).
stock guy
Tuesday, March 06, 2007
 
 
Don't use Symbols as identifiers.

  - they're not unique.  Symbols can be reused on different exchanges
  - they change.  If a stock is suspended temporarily, its symbol changes.  If a company is bought, its symbol may change.
  - they're reused.  SE used to be 7-11, now 7-11 is private and somebody else uses that symbol
Warlock Send private email
Tuesday, March 06, 2007
 
 
I agree with Warlock, using the symbol is a bad idea - especially if you're planning on including securities traded on exchanges in several countries.

The three main 'standard' ids for securities are CUSIP, SEDOL and ISIN.  CUSIPs are typically used on US exchanges and SEDOLs on the UK and Irish exchanges (although they seem to be used for most non-US exchanges).  The ISIN is the latest standard, designed to be useful for all exchanges (although there seem to be some implementation issues).

The Wikipedia has information on all three:
http://en.wikipedia.org/wiki/CUSIP
http://en.wikipedia.org/wiki/SEDOL
http://en.wikipedia.org/wiki/International_Securities_Identifying_Number

If I were to design a new trading system, the ISIN would be the main identifier with additional fields for the CUSIP, SEDOL and exchange.
RocketJeff Send private email
Tuesday, March 06, 2007
 
 
OTOH:

- The symbol is what the rest of the world uses to identify this particular "object".  While it has drawbacks (see below), they are relatively minor and easily dealt with. If you use a different identifier you will spend much time translating back and forth.

- While symbols do change, that is relatively infrequent, and is typically accomplished by running some kind of batch job against the db to change all open orders, etc.
BillT Send private email
Tuesday, March 06, 2007
 
 
You use the symbol (and country, exchange, etc) to communicate to the user.  The identifier is used both within the system and as the preferable way of identifying the security when communicating to external systems (i.e. exchanges or brokers).

This way the changes to security symbols don't impact the entire system - just what the user sees.  If the system is only used internally (an in-house trading system, you might not even have to follow every symbol change (NASDAQ symbols have characters when SEC filings are late or based on the financial status of the company).
RocketJeff Send private email
Tuesday, March 06, 2007
 
 
If you get into options, symbols become much more problematic. For one thing, the symbol for an option is often the same as the symbol for an unrelated stock (even in the same country). And symbols for a given option change all the time for various reasons. This is just the tip of the iceberg in dealing with options and trading / clearing systems.
Greg Send private email
Tuesday, March 06, 2007
 
 
Why is "never use external identifiers as a database id" so hard for people to grasp?  It does not hurt you in the least to follow it dogmatically, and it saves your bacon so many times it's like "look both ways before crossing the street," or "wash your hands after going to the bathroom."
Art Send private email
Wednesday, March 07, 2007
 
 
there is always ON UPDATE CASCADE, though.
Matt
Wednesday, March 07, 2007
 
 
Thanks guys, I did choose the ID route already, but needed more opinions to make sure it is ok. Sometimes I feel I need to do too many joins / Symbol <-> ID conversions (although the conversions are cached in the application so it's not really a performance problem).
stock guy
Thursday, March 08, 2007
 
 
The ids I've worked with are described in http://en.wikipedia.org/wiki/Reuters_Instrument_Code

Basically, Reuters needed a ticker symbol system that allows them to talk about equities (on one or more exchanges), options, futures and indicies (and the derivatives of indicies) all at the same time.


They're pretty standard and most of the world should be able to translate them into their local representation.
Katie Lucas
Thursday, March 08, 2007
 
 
Use a surrogate integer id!
Steve Moyer Send private email
Thursday, March 08, 2007
 
 
If you're not competent to do some simple data modelling like this without consulting a message board, why the hell are you (presumably) getting paid a fortune to implement trading systems?
Booo
Saturday, March 10, 2007
 
 
>> If you're not competent to do some simple data modelling like this without consulting a message board, why the hell are you (presumably) getting paid a fortune to implement trading systems?

Well, at least he's looking for information about the best way to do it - I wish the people that wrote several of the (very successful) trading systems I used had done that.

The last systems I worked with (very costly and highly recommended) stored dates as character strings (MM-DD-YY format), uses floats to store currency values, sometimes stores characters in integer fields (i.e. 0x20 is a space, 0x41 is 'A', etc) and a whole list of inconsistencies in the naming of fields.
RocketJeff Send private email
Monday, March 12, 2007
 
 
"there is always ON UPDATE CASCADE, though"

There's a lesson in there. Just because something is programaticaly easy doesn't mean that it's either a good idea or an efficient methodology. This might apply nowhere as much as it does in relational databases.
David Aldridge Send private email
Wednesday, March 14, 2007
 
 
Just amazed at the level of detail doing things correctly can require.
frustrated
Monday, March 19, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz