July 9, 2009 at 2:09 pm
Lamprey13 (7/9/2009)
Whitefang, is that you?
Don't think so. There's been no mention of "Enterprise Application" or "Entry level DBA" yet. 😀
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2009 at 2:14 pm
People don't get married anymore and change their name?
N 56°04'39.16"
E 12°55'05.25"
July 10, 2009 at 7:55 am
I have clarified what I mean by pointer-based foreign keys on the SQL feedback page
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=472957
It is not the network model and it completely eliminates the use of a surrogate identifier.
In RDBMS, when you want to relate a table to another table, you must duplicate the PK of the referenced table as foreign keys in the referencing table. Why do we have to do this? This is something an RDBMS should "maintain internally". This creates a massive maintenance issue when we decide to use natural keys for PK where a PK can involve 3-4 candidate keys.
There is absolutely no point to duplicate the FK in the referencing tables. SQL queries should not be affected because the FK is there in the referencing tables "internally".
SELECT * FROM [referencing] AS a INNER JOIN [referenced] AS b ON
a.FK1 = b.PK1 AND a.FK2 = b.PK2 AND a.FK3 = PK3 should not be affected by this modification.
In fact, the modification could be further improved by allowing joins on "designated" "candidate keys".
What I mean is
designated_pk consist of columns (col1, col2, col3) marked as a primary key
SELECT * FROM [referencing] AS a INNER JOIN [referenced] AS b ON a.designated_pk = b.designated_pk - the join is done by the name of the PK constraint
EDIT: I meant to say the join condition should be
SELECT * FROM [referencing] AS a INNER JOIN [referenced] AS b USING designated_pk
CREATE TABLE referencing
(
reference_id NOT NULL PRIMARY KEY etc etc
CONSTRAINT RELATE TO referenced (designated_pk)
);
CREATE TABLE referenced
(
col1 NOT NULL,
col2 NOT NULL,
col3 NOT NULL,
col4 NOT NULL,
CONSTRAINT designated_pk AS PRIMARY KEY (col1, col2, col3)
);
This has already been implemented in Sybase new architecture and other TRDBMS directly from Celko:
The way we get around it is with optimistic schemes, parallelism (ever
work with Tandem and the products that followed?) or with Sybase (nee
WATCOM) SQL's pointer chain approach.
In the Sybase model, a PK value appears only once in a base table. FK
references to it are proper pointer chains to that base table. They
do at the engine level what you are faking manually in code with those
IDENTITY columns. I change one value, in one table and it cascades
thru the entire schema instantly. My only worry is that an
application might have read the old value, kept it in local program
storage and then tried to use it because the application allowed dirty
reads.
July 10, 2009 at 9:03 am
It doesn't eliminate the use of a surrogate identifier, it just hides it from you. That's even per the quote from Celko that you include in your post.
Would be nice to be able to hide it when I want to. Can't say that it matters much, and it would actually have disadvantages more often than advantages, from what I can see. But it would still be nice in certain, limited circumstances.
Outside of a few minor applications, I still see it as a way for the DBA to lie to himself about using "pure relational data" when he really isn't.
- 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
July 10, 2009 at 9:07 am
You are doing the job of the DB Engine in the worst possible way (using fake pointers identifiers). Why stop there? Why not use text files for storage and write your own sequential parser?
I can see your DBA's lieing to themselves about relational data and using bit flags all over the place. I'm sure Celko knows more about designing a relational model than your bit-flag DBAs.
July 10, 2009 at 9:17 am
sqlguru (7/10/2009)
You are doing the job of the DB Engine in the worst possible way (using fake pointers identifiers). Why stop there? Why not use text files for storage and write your own sequential parser?I can see your DBA's lieing to themselves about relational data and using bit flags all over the place. I'm sure Celko knows more about designing a relational model than your bit-flag DBAs.
Again, if SQL Server is so bad, why are you using it or even working with it? Go work with the other RDBMS's that you think are better.
July 10, 2009 at 9:20 am
sqlguru (7/10/2009)
You are doing the job of the DB Engine in the worst possible way (using fake pointers identifiers). Why stop there? Why not use text files for storage and write your own sequential parser?I can see your DBA's lieing to themselves about relational data and using bit flags all over the place. I'm sure Celko knows more about designing a relational model than your bit-flag DBAs.
In what way is using explicit pointers "the worst possible way" as compared to hidden pointers? It's the exact same solution, except that one is hidden and one isn't. There's no other difference.
If I could write a parser that would get the same job done as I can get with SQL Server, I very well might do so. I can't. That's why I use MS SQL, instead of customizing my own engine from MySQL or some other open source solution.
On your second point, I have no clue what you're talking about. Who's using bit flags all over the place? How does that even fit into the subject at hand? What's it got to do with anything at all?
Honestly, it sounds to me like you need to escape from the world of commercial RDBMSs and start building your own engine. You could start with MySQL, and make it do EXACTLY what you need. Then, market and sell it.
You'll be much happier with that.
If you can make it compete with Oracle and MS-SQL and DB2, you'll have proven your point, and you'll get the exact result that you want. As people switch to your database, for whatever reasons, they will be throwing out the fake and wannabe DBs that you hate so much.
That is the exact strategy that you need to start following. It's the only way that you'll get what you want in exactly the way that you want it. And there's nobody stopping you from doing so.
Go for it. Let us know when it's ready to use, and I'll certainly take a look at it and see if it's convincing enough to move away from T-SQL to whatever you'll call your version.
I am not being sarcastic or insulting. I'm offering real advice that will allow you to achieve your stated goal.
- 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
July 10, 2009 at 10:09 am
As far as the "Pointer-based foreign keys", this implementation is described in the article "SQL Anywhere Cost Based Query Optimizer"
for version 5 at http://m.sybase.com/detail?id=1003010. The specific paragraph is:
4.1. Indexes
There are two types of indexes in SQL Anywhere. A primary
key index of a table includes index entries for each row and
index entries for the foreign keys from other tables that
refer to that row's primary key value. Thus a primary key
index not only serves to index rows by primary key value but
also serves as the mechanism to enforce referential
integrity constraints.
I asked Sybase about the status of this implementation and they responded
This architecture was dropped in the Version 7.0 release, which was GA'd
in February 2000.[\quote]
Based on my working with SQL Anywhere back in the 1996 to 1999 time frame, the old array FK implementation always caused performance problems and extra indexes had to be added.
SQL = Scarcely Qualifies as a Language
July 10, 2009 at 11:24 am
Another 4 page rant over here
N 56°04'39.16"
E 12°55'05.25"
July 10, 2009 at 11:46 am
Peso (7/10/2009)
Another 4 page rant over here
Perhaps we should take their approach, and ask Steve to close this thread as well. I do have to admit I liked reading that rant.
July 10, 2009 at 12:27 pm
Well, it's an interesting debate. I can understand why dbforums might think it is worth closing it, but I think it goes against my grain to do so.
My feeling in reading this is there is a fundamental disagreement about whether or not there is value in these suggestions, or that they even make sense. While I don't think sqlguru has done a good job presenting the argument here or on dbforums, I do think he/she has the right to debate it if anyone is willing to do so.
At this point I don't think this thread has degenerated enough to close. If you would like to continue to debate, feel free. If not, please feel free to unsubscribe.
July 10, 2009 at 3:08 pm
Or, perhaps, if starts into a flame war? Hopefully not. And it stays away from the personal attacks as well.
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply