May 14, 2008 at 10:07 am
I have a table with (for simplicity reason) 4 columns:
id -- just surrogate primary key
FirmNum -- can appear multiple times and it forms firm group, it is FK from another table
Ticker -- is actual ticker abbreviation. Each firm can have one or multiple tickers.
IsPrimary -- boolean, indicates whether a ticker is the primiry within each firm group of records.
There are special rules apply to IsPrimary :
1. Each firm must have a primary ticker.
2. There should be only one primary ticker for a firm.
3. If a record that is being enetered is the only one for a firm the default for primary should be 1 (later on it can be overwritten).
What would be the best way to implement these rules? So far I'm thinking to use trigger (or a set of them). But can it be done with a combination of check and default constraints ? What would be better solution ?
Here is a code to create testing data:
if object_id('test') is not null
drop table test
go
create table test
(
id int identity,
FirmNum int,
Ticker varchar(4),
IsPrimary bit
)
insert into test (FirmNum, Ticker, IsPrimary)
select 1,'ABC',1 union
select 2,'DEF',1 union
select 2,'KLM',0 union
select 2,'XYZ',0 union
select 3,'AAA',0 union
select 3,'BBB',1 union
select 4,'ABCD',0 union
select 4,'KLMN',0 union
select 4,'PRST',1 union
select 4,'WZYX',0
go
select * from test
Thanks
May 14, 2008 at 11:14 am
You could do this with a check constraint. That is prbably what I would lean toward. I think you could probably get better performance out of a well-designed trigger, but a constraint will be nice and clean.
As a tip for the direction to go with a constraint or triggger...it is really just that the SUM of your flag for the FirmNum = 1.
May 14, 2008 at 11:58 am
If this was my project, I don't think that I would try to implement these rules in the DBMS. While it's possible to do so, the question is do you want to?
Dealing with this at the SQL level, you end up either making an arbitrary decision to change input data (e.g. application sends down a new record as primary, but there already is one, so it is changed to non-primary) or you reject back to the application, which will have to be coded to handle it.
I would prefer to handle it in the app at the start. I would want to avoid the overhead of all the extra reads for every update/insert/delete when chances are good I have already retrieved this info for the app anyway.
Hopefully that makes sense.
GL!
May 14, 2008 at 12:02 pm
Michael Earl (5/14/2008)
You could do this with a check constraint. That is prbably what I would lean toward. I think you could probably get better performance out of a well-designed trigger, but a constraint will be nice and clean.As a tip for the direction to go with a constraint or triggger...it is really just that the SUM of your flag for the FirmNum = 1.
Just keep in mind that you can't sum() a bit field, if that applies here.
- 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
May 14, 2008 at 12:17 pm
Yes, you are right - it would need to be a TinyInt to sum it.
As far as the "do I put the logic here or in the application?" question, there are good arguments for both sides.
As I am primarily a database developer, I tend to want to put anything I would consider data integrity at the lowest level feasible to help deal with multiple applications touching data and possibly not having all of their business rules in sync.
There can be an argument for moving data integrity into business logic layers of your application - this is the same argument often used for not having database-level foreign key constraints (although your constraint is arguably more of a business logic rule than a foreign key is).
This decision is something you will have to make for yourself.
May 14, 2008 at 12:47 pm
Personally, I'd keep it simple and add primaryTicker to the Firm table. Then a simple fkey relationship will meet your business rules and it should be dead simple for an application to implement. The FirmTickers table then becomes just a collection of (FirmNum, Ticker).
May 14, 2008 at 12:52 pm
antonio.collins (5/14/2008)
Personally, I'd keep it simple and add primaryTicker to the Firm table. Then a simple fkey relationship will meet your business rules and it should be dead simple for an application to implement. The FirmTickers table then becomes just a collection of (FirmNum, Ticker).
Clever! I like 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
May 14, 2008 at 12:58 pm
Thank all of you for your input.
Yes, and I also got error while trying to sum on bit. But I would not like to switch it to tinyint in order to not allow users to enter anything greater than 1.
And it is not such an easy thing to write a check constraint accross multiple records. Now I am writing triggers for that. But since you are suggesting to move all this logic to application side, I am thinking that maybe the most appropriate place for this is inserting/updating stored procedures which will be called from application. However, there is some drowback with stored procs: today we have just one application, that's fine. But if at some day they will create another one accessing same tables, I don't know whether they will use my stored proc.
May 14, 2008 at 12:59 pm
Interesting that you think that is simpler. It makes a circular reference - meaning you cannot insert the Firm record without already having a Firm/Ticker record to be the primary, but you cannot insert the Firm/Ticker record without the Firm record already being there. This would men one of your foreign keys could not have an actual FK constraint - or your Firm record would ahev to allow NULL for the primary ticker (violating one of your business rules).
In Oracle, you could get past this with deferred constraints, but I tend to lean toward the thought that a circular reference is a violation of basic database design rules (parent/child record relationships should be clearly-defined) and thus circular references should be avoided.
May 14, 2008 at 1:09 pm
While I was typing my reply, Antonio suggested an idea of placing primary ticker in to Firm table.
OK, but we want to have tickers together, in one place, which is Ticker table. Or primary tickers will be replicated in both tables ? In this case, the stored procedure / triggers will grow even much more complex.
May 14, 2008 at 1:12 pm
Michael Earl (5/14/2008)
Interesting that you think that is simpler. It makes a circular reference - meaning you cannot insert the Firm record without already having a Firm/Ticker record to be the primary, but you cannot insert the Firm/Ticker record without the Firm record already being there. This would men one of your foreign keys could not have an actual FK constraint - or your Firm record would ahev to allow NULL for the primary ticker (violating one of your business rules).In Oracle, you could get past this with deferred constraints, but I tend to lean toward the thought that a circular reference is a violation of basic database design rules (parent/child record relationships should be clearly-defined) and thus circular references should be avoided.
the fkey is from [Firm] to [Ticker], not [Firm] to [FirmTicker], so there is no circular reference.
May 14, 2008 at 1:36 pm
Yup - you are right.
My bad.
May 14, 2008 at 2:33 pm
Are Tickers dependent on Firms?
As I look at the original presentation, it seems to me that part of the definition of the Ticker is the Firm that it belongs to. In which case, we cannot have a Ticker before a Firm. This means that Antonio's solution using PrimaryTicker in Firms would require a nullable column as the Ticker would not be available at Insert. This presents its own set of issues to be aware of and plan for.
Am I making sense or am I missing something here?
May 14, 2008 at 2:40 pm
assuming 'ticker' is a stock trading symbol, then a firm can certainly exist without a 'ticker'. tickers would be somewhat independent of firms since they can be reused (especially with penny stocks). likewise, tickers change over time (usually when a company renames itself or reissues stock or is delisted from a major exchange).
whether this description applies to this situation is a question for the original poster.
May 14, 2008 at 2:46 pm
I totally agree Antonio.
Great example of why we tend to look for plenty of DDL around here! 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply