locked
Database design using unique identifiers and primary key RRS feed

  • Question

  • I am beginner trying to design a database using SQL Server 2005.  I have three tables:

    AdminMember
    AMem_AdminId (uniqueidentifier, PK)
    AMem_UserName
    AMem_Password

    CompAcct
    Comp_CompId (uniqueidentifier, PK)
    Comp_Website
    Comp_Name

    AdminCompActt
    AdCA_AdminId
    AdCA_CompID

    The AdminMember table holds all Admins.  The CompAcct table holds all companies.

    I'm trying to use the AdminCompAcct table to link the above referenced tables.  This way I can keep track of each Admin, each Company, and which Company each Admin belongs to. 

    The issue I'm running into is how to link them according to best practices.  Because both Amem_AdminID and Comp_CompID are uniqueidentifiers and PK, it seems I cannot create both AdCA_AdminID and AdCA_CompId as a FK.  The first one works fine but the second one generates an error. (The columns in table 'AdminCompAcct' do not match an existing primary key or UNIQUE constraint). 

    I understand the error message but what is the best way resolve or design this?  Thanks for your help.

    Brian

    Wednesday, April 28, 2010 5:45 PM

Answers

  • Logically it should work. Here is some basic SQL that will generate this without error:
    CREATE TABLE AdminMember
    (
     AMem_AdminId	INT IDENTITY PRIMARY KEY,
     AMem_UserName	VARCHAR(50),
     AMem_Password	VARCHAR(50)
    )
    
    CREATE TABLE CompAcct
    (
     Comp_CompId	INT IDENTITY PRIMARY KEY,
     Comp_Website	VARCHAR(50),
     Comp_Name	VARCHAR(50)
    )
    
    CREATE TABLE AdminCompActt
    (
     AdCA_AdminId	INT REFERENCES AdminMember,
     AdCA_CompID	INT REFERENCES CompAcct,
     PRIMARY KEY(AdCA_AdminId, AdCA_CompID)
    )

    • Marked as answer by bcahill Wednesday, April 28, 2010 6:39 PM
    Wednesday, April 28, 2010 6:31 PM
    Answerer

All replies

  • Logically it should work. Here is some basic SQL that will generate this without error:
    CREATE TABLE AdminMember
    (
     AMem_AdminId	INT IDENTITY PRIMARY KEY,
     AMem_UserName	VARCHAR(50),
     AMem_Password	VARCHAR(50)
    )
    
    CREATE TABLE CompAcct
    (
     Comp_CompId	INT IDENTITY PRIMARY KEY,
     Comp_Website	VARCHAR(50),
     Comp_Name	VARCHAR(50)
    )
    
    CREATE TABLE AdminCompActt
    (
     AdCA_AdminId	INT REFERENCES AdminMember,
     AdCA_CompID	INT REFERENCES CompAcct,
     PRIMARY KEY(AdCA_AdminId, AdCA_CompID)
    )

    • Marked as answer by bcahill Wednesday, April 28, 2010 6:39 PM
    Wednesday, April 28, 2010 6:31 PM
    Answerer
  • That worked great.  I see you created one primary key for two columns.  Is this an okay way to design the database?  Is the direction I'm taking reasonable?

    Thanks!

    Wednesday, April 28, 2010 6:45 PM
  • This is known as a composite key. It is not only valid, it is correct.

     

    Some may argue and want to add another id for convenience (which is debatable). Regardless, from a modeling standpoint, this is the PK.

    Wednesday, April 28, 2010 7:24 PM
    Answerer
  • Awesome, thank you.  I was bashing my brain in on this. 
    Wednesday, April 28, 2010 7:30 PM
  • If you have another ID as a PK, how can you set two columns to be unique?

    Thanks.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, April 28, 2010 7:33 PM
    Answerer
  • With a UNIQUE CONSTRAINT (or INDEX).

    CREATE TABLE A(A INT PRIMARY KEY, B INT, C INT, UNIQUE(B, C));

    Assuming you meant *why* instead of *how*, at times i'm just as bewildered. :)

    • Edited by Brian TkatchEditor Wednesday, April 28, 2010 7:37 PM added C so it actually meant something. :)
    Wednesday, April 28, 2010 7:36 PM
    Answerer
  • I meant, altering the existing table. I found that our existing table has duplicates and the two columns must be unique.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, April 28, 2010 7:41 PM
    Answerer
  • Why not just verify there are no duplicates and ADD the UNIQUE CONSTRAINT?

    Wednesday, April 28, 2010 7:50 PM
    Answerer
  • There are duplicates. I deleted them and now I scanned several pages of BOL already looking for exact syntax of the command to add this damn constraint.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, April 28, 2010 8:06 PM
    Answerer
  • I just tried this:

    CREATE TABLE A(A INT PRIMARY KEY, B INT, C INT);

    ALTER TABLE A ADD CONSTRAINT aaa UNIQUE(B, C);

    Seems to work in 2005.

    • Proposed as answer by Naomi NEditor Wednesday, April 28, 2010 8:20 PM
    Wednesday, April 28, 2010 8:11 PM
    Answerer
  • Thanks, I was just about to try it as well once I figured out the original problem.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, April 28, 2010 8:21 PM
    Answerer
  • One extra question. If we have several constraints, what does define the order of execution?

    Say, I found that this table already has a constraint defined that uses UDF (but this doesn't exist in the database I'm looking for, presumably exist in another database). I'm going to check the function script now, but I think we still need a unique constraint and I prefer it to execute first and then the second check for only one address of each address type for particular guest.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, April 28, 2010 8:33 PM
    Answerer
  • You don't have any control over the firing of constraints, though the unique constraint will go first because check constraints are evaluated after the row is in the table, while the unique constraint will fire when the row is attempted to be added to the unique index that is the method of enforcement of the constraint.

    I don't think that you can use a UDF that accesses a different database in a constraint though.. A trigger yes..  Or am I confused there?


    Louis

    • Proposed as answer by Naomi NEditor Thursday, April 29, 2010 12:49 PM
    Thursday, April 29, 2010 5:00 AM
  • I think he answered your question, but keep in mind that if you implement this:

    CREATE TABLE AdminMember
    (
    AMem_AdminId INT IDENTITY PRIMARY KEY,
    AMem_UserName VARCHAR(50),
    AMem_Password VARCHAR(50)
    )
    That

    insert AdminMember( AMem_UserName, AMem_Password)
    values ('fred','password')
    insert AdminMember( AMem_UserName, AMem_Password)
    values ('fred','password')
    insert AdminMember( AMem_UserName, AMem_Password)
    values ('fred','password')
    insert AdminMember( AMem_UserName, AMem_Password)
    values ('fred','password')

    Will be perfectly acceptable (same with using a guid as your model has, I just took Brian's code :)). It is a best practice to use a unique constraint on a non-automatically generated column so the actual natural key of the table is protected.  Otherwise, how with the user tell which fred is the real one?

    Another possibility would be to use UserName as your primary key, though I am assuming that you have some reason to use the guid for your primary key (like some tool that uses them?)


    Louis

    Thursday, April 29, 2010 5:06 AM
  • "Otherwise, how with the user tell which fred is the real one?"

    Who cares? Fred isn't the important part, that's just a name. Unless the sign-in is done by name too.

    "It is a best practice to use a unique constraint on a non-automatically generated column so the actual natural key of the table is protected. "

    Actually, best practice is to use the natural key, if there is one, as the primary key. If that is not possible, add a id as well.

    This is what we talked about last time. Just wanted to comment on this more concrete example.

    Thursday, April 29, 2010 12:04 PM
    Answerer
  • Thanks, Louis. We have a constraint depending on one extra table. There are 3 tables involved : Guests, AddLink (junction table) and Address. Address table has the Address Type column. Each guest can only have one Address of a cetrain type. Therefore there is a UDF constraint on AddLink table checking Address table.

    There is no unique constraint in AddLink table on Address_ID, guest_no combination. My suggestion was to add this constraint, though on the other hand the UDF constraint implicitly disallows duplicates too. I did find duplicates in AddLink table, but I think they were created before this constraint was introduced.

    So, do you think it would make sense to add an extra unique constraint?

    Thanks again.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, April 29, 2010 12:53 PM
    Answerer
  • I'm  sure you know what i would do.

    Drag address_type down to the AddLink (FKing it with address), and add the UNIQUE CONSTRAINT there.

    Thursday, April 29, 2010 4:16 PM
    Answerer
  • Do you mean it's possible to do visually without changing structure of the tables? Let me try.

    UPDATE. I don't see a way to do this visually, can you please write instructions?


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    Thursday, April 29, 2010 4:29 PM
    Answerer
  • No, it'd change the structure.

    Heh, i wonder if you could just do it with an INDEXED VIEW.  Same difference.

    Thursday, April 29, 2010 4:31 PM
    Answerer
  • I understand. I think I had an idea of changing structure, but this is clearly not an option for our application. OK, so my question is - is the existing UDF constraint enough or adding an extra unique constraint may be helpful?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, April 29, 2010 4:35 PM
    Answerer
  • "though on the other hand the UDF constraint implicitly disallows duplicates too."

    True. But you want the UNIQUE CONSTRAINT to cancel the addition and thus save the cost of running the FUNCTION. Hmm... interesting questions. I agree with Louis, we just don't know, but we can guess.

    And why not just test it already:

    SQL Server 2005

    CREATE TABLE Guest(Id INT PRIMARY KEY);
    CREATE TABLE Address(Id INT PRIMARY KEY, Type INT NOT NULL);
    
    CREATE TABLE AddLink
    (
     Id		INT IDENTITY PRIMARY KEY,
     Guest		INT REFERENCES Guest,
     Address	INT REFERENCES Address
    );
    
    GO
    
    CREATE FUNCTION Address_Type_Amount()
    RETURNS BIT
    AS
    BEGIN
     RETURN
     (
    	 SELECT
    		CASE COUNT(*) WHEN 0 THEN 0 ELSE 1 END
    	 FROM
    		(
    		 SELECT
    				1
    		 FROM
    				AddLink,
    				Address
    		 WHERE
    				Address.Id = AddLink.Address
    		 GROUP BY
    				Address.Type
    		 HAVING
    				COUNT(*) > 1
    		) Address_Type(Amount)
     );
    END;
    
    GO
    
    ALTER TABLE AddLink ADD CONSTRAINT Check_Type CHECK(dbo.Address_Type_Amount() = 0);
    
    INSERT INTO Guest SELECT 1;
    INSERT INTO Address(Id, Type)
     SELECT 1, 1 UNION ALL
     SELECT 2, 2 UNION ALL
     SELECT 3, 1;
    
    INSERT INTO AddLink(Guest, Address)
     SELECT 1, 1 UNION ALL
     SELECT 1, 2;
    
    INSERT INTO AddLink(Guest, Address) VALUES(1, 1);
    /*
    Msg 547, Level 16, State 0, Line 1
    The INSERT statement conflicted with the CHECK constraint "Check_Type". The conflict occurred in database "bmi", table "dbo.AddLink".
    The statement has been terminated.
    */
    
    ALTER TABLE AddLink ADD CONSTRAINT UQ UNIQUE(Guest, Address);
    INSERT INTO AddLink(Guest, Address) VALUES(1, 1);
    /*
    Msg 2627, Level 14, State 1, Line 1
    Violation of UNIQUE KEY constraint 'UQ'. Cannot insert duplicate key in object 'dbo.AddLink'.
    The statement has been terminated.
    */
    
    DROP TABLE AddLink;
    DROP FUNCTION Address_Type_Amount;
    DROP TABLE Guest;
    DROP TABLE Address;
    
    
    So, the UNIQUE CONSTRAINT definitely goes first. Does it short circuit the second? I have no idea how to check that.

    • Proposed as answer by Naomi NEditor Thursday, April 29, 2010 6:30 PM
    Thursday, April 29, 2010 6:09 PM
    Answerer
  • Actually, it's a novel idea to test - for some reason didn't occur to me :)

    I'll try to do a big insert first without this new unique constraint and then with it and compare the time difference. I'm almost certain that with UNIQUE constraint in place it will be much quicker.

    Will post update once I'm done.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, April 29, 2010 6:33 PM
    Answerer
  • Look at me, i'm testing stuff. Whee! :P

    I understand the structure of the TABLE can't change. But ADDing the UNIQUE CONSTRAINT will CREATE a UNIQUE INDEX, storing both of those COLUMNs.

    For the same price, why not ADD an INDEXed VIEW, which will *also* obviate the FUNCTION-based CONSTRAINT. Two for the price of one, no?

     

     

    CREATE TABLE Guest(Id INT PRIMARY KEY);
    CREATE TABLE Address(Id INT PRIMARY KEY, Type INT NOT NULL);
    
    CREATE TABLE AddLink
    (
     Id		INT IDENTITY PRIMARY KEY,
     Guest	INT REFERENCES Guest,
     Address	INT REFERENCES Address
    );
    
    GO
    
    CREATE VIEW Guest_Address_Type WITH SCHEMABINDING
    AS
     SELECT
    		AddLink.Guest,
    		Address.Type
     FROM
    		dbo.AddLink,
    		dbo.Address
     WHERE
    		Address.Id = AddLink.Address;
    
    CREATE UNIQUE CLUSTERED INDEX Guest_Address_Type_UQ ON Guest_Address_Type(Guest, Type);
    
    INSERT INTO Guest SELECT 1;
    INSERT INTO Address(Id, Type)
     SELECT 1, 1 UNION ALL
     SELECT 2, 2 UNION ALL
     SELECT 3, 1;
    
    INSERT INTO AddLink(Guest, Address)
     SELECT 1, 1 UNION ALL
     SELECT 1, 2;
    
    INSERT INTO AddLink(Guest, Address) VALUES(1, 1);
    /*
    Msg 2601, Level 14, State 1, Line 1
    Cannot insert duplicate key row in object 'dbo.Guest_Address_Type' with unique index 'Guest_Address_Type_UQ'.
    The statement has been terminated.
    */
    
    INSERT INTO AddLink(Guest, Address) VALUES(1, 3);
    /*
    Msg 2601, Level 14, State 1, Line 1
    Cannot insert duplicate key row in object 'dbo.Guest_Address_Type' with unique index 'Guest_Address_Type_UQ'.
    The statement has been terminated.
    */
    
    DROP VIEW Guest_Address_Type;
    DROP TABLE AddLink;
    DROP TABLE Guest;
    DROP TABLE Address;
    
    

     

    • Proposed as answer by Naomi NEditor Thursday, April 29, 2010 6:52 PM
    Thursday, April 29, 2010 6:48 PM
    Answerer
  • Wow, yes, very nice. I guess I never used indexed views before, didn't even think about it.

    The problem is - we need to maintain this database on SQL Server 2000 (for few more months). Will it work there?


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, April 29, 2010 6:54 PM
    Answerer
  • There is no free lunch with indexes...as the age-old dba saying goes....

    If the underlying tables are dynamic OLTP tables, indexed view may cause performance issues.

    For static tables, indexed views are OK.


    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    • Edited by Kalman Toth Thursday, April 29, 2010 7:07 PM
    Thursday, April 29, 2010 7:03 PM
  • Yes, they are dynamic OLTP tables. So, what would be your suggestion:

    two constraints (assuming the first short-cut the second UDF based) or indexed view?

    Also, the DB should work in SQL Server 2000.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, April 29, 2010 7:06 PM
    Answerer
  • I have no idea if it will work in 2000. I tested it in 2005.

     

    A quick google search yielded this article . Why not give it a shot?

    • Proposed as answer by Naomi NEditor Thursday, April 29, 2010 8:13 PM
    Thursday, April 29, 2010 7:33 PM
    Answerer
  • Indexed views work in SQL Server 2000.
    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    Thursday, April 29, 2010 7:37 PM
  • Look, it is a very common practice to use a surrogate key... very common (and considered best practice by a lot of people, again, not just a few). UserName is probably a good example a case where the natural key would be a bad idea. User name changes, you then have to change 100s, even 10000000s of references.  With a surrogate, you update 1 piece of data, and essentially all references are changed.

    So assuming a person chooses to use a surrogate key (however insane that might be), allowing all non-surrogate column values to be repeated is a really horrible practice, and cannot be defended. And any key that can be defined ought to be.  There is a reason why the people who came up with the concept of a key did not limit them to one. The term candidate key is not one that I came up with, nor is the concept of a UNIQUE constraint.  I agree with you that you only NEED one key, but if multiple possible keys are there due to the design of a table, they should be protected.


    Louis

    Friday, April 30, 2010 4:23 AM
  • "a surrogate key... very common (and considered best practice by a lot of people, again, not just a few)."

    I think that explains why most databases i have seen have poor design. They just don't "get it". Surrogate keys have their place. They may even be "common practice" due to understandability. But "best practice"? Say it ain't so!

    "UserName is probably a good example a case where the natural key would be a bad idea. User name changes,"

    That is an excellent reason to not key it. I meant specifically the argument "how with the user tell which fred is the real one?" was unconvincing.

    "And any key that can be defined ought to be."

    That depends. :)

    If it can be defined *inherently*, yes! The more constraints, the more this entity is defined.
    If it can defined because of the data, no.

    "The term candidate key is not one that I came up with,"

    Although, i never hear the term before working with SQL Server. Not that it isn't there in some fashion, but SQL Server has its own approach.

    "but if multiple possible keys are there due to the design of a table, they should be protected."

    That's where i disagree. Key either are there logically or they aren't. That is, they are inherent, or they are not. The only time we ever use key that isn't inherent, is when there is no natural PK, and the TABLE needs a PK (which is the usual case, but "log-tables" don't need one.). In which case, a surrogate is ADDed. But, if there is a surrogate, there is no natural. If there is natural, there is no surrogate.

    An exception can be made when a (composite) natural PK is too large. I'd still argue against it until performance issues crop up (or have been proven with the dataset), or the composite-key size limit is hit. In either case, these are based on the RDBMS limitations, and not something that "should" be the case.

    Friday, April 30, 2010 12:16 PM
    Answerer
  • >>"The term candidate key is not one that I came up with,"

    Although, i never hear the term before working with SQL Server. Not that it isn't there in some fashion, but SQL Server has its own approach.<<

    This statement confuses me as to where you learned about database design. The term candidate key was coined by Codd, the person who originated the concept of a relational database.

    It is referenced here in an paper written back in 1975:

    The candidate keys, as defined by E. F. Codd [4], are important in the process of reducing a normalized relation into second and third normal forms. http://portal.acm.org/citation.cfm?id=500109&dl=GUIDE&coll=GUIDE&CFID=88546523&CFTOKEN=37674479

    Reference 4 is Further Normalization of the Data Base Relational Model, written by Codd.  It is a very common term and a very important one.

    As for surrogates, again, not something that was created by me.  Codd too introduced them.  They are often poorly used, and it is certainly debatable as to whether they are to be used in all or even ANY cases, but the concept itself is not new.  Tony Rogerson has a good article here:

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2010/03/13/database-design-surrogate-keys-part-1-of-many-rules-for-surrogate-keys-e-f-codd-and-c-j-date-research-and-problems-they-solve.aspx

    But as you mention here, you suggest a surrogate is good in this case:

    >>An exception can be made when a (composite) natural PK is too large. I'd still argue against it until performance issues crop up (or have been proven with the dataset), or the composite-key size limit is hit. In either case, these are based on the RDBMS limitations, and not something that "should" be the case.<<

    So are you saying that you would not include a UNIQUE constraint on the natural key? And if not, what about duplicates?


    Louis

    Friday, April 30, 2010 3:17 PM
  • "The term candidate key was coined by Codd, the person who originated the concept of a relational database."

    I never read Codd. And based on the attitude of his most vocal supporter, i probably never will.

    Regardless, i don't think i ever heard the term used before i started looking at SQL Server. To me, that  reflects an attitude toward design, showing that different people approach the DM differently in the different RDBMSs. I never design with candidate keys. The PRIMARY KEY is generally obvious. Or, put another way, the definition of the TABLE (in the logical model) is based on the PK. If you have to search for the PK, the TABLE probably isn't designed very well.

    >So are you saying that you would not include a UNIQUE constraint on the natural key? And if not, what >about duplicates?

    In that case, i would. Because the uniqueness is inherent.

    Friday, April 30, 2010 3:45 PM
    Answerer
  • >>"The term candidate key was coined by Codd, the person who originated the concept of a relational database."

    I never read Codd. And based on the attitude of his most vocal supporter, i probably never will.<<

    Codd is the guy who came up with relational database in the first place. I am by no means his "most vocal supporter".  I am just trying to find common ground in the fundamentals so we can converse about database design topics.

    It is like studying physics but having never heard of Newton.  Sure his principles have been refined over the year by people like Einstein, but Newton is the father of physics, and Codd is the same to Relational databases.


    Louis

    • Proposed as answer by Naomi NEditor Tuesday, May 4, 2010 3:23 AM
    Tuesday, May 4, 2010 3:14 AM
  • "The term candidate key was coined by Codd, the person who originated the concept of a relational database."

    .... I never design with candidate keys.


    Codd's 12 rules:

    http://social.msdn.microsoft.com/Forums/en-US/sqldocumentation/thread/5e2bebff-886b-4c65-89d9-01bed19067f3

    SSIS 2008 data profiler actually uses the term "candidate key" to identify potential Primary Key-s. When you get a feed and know nothing about the data, it is really helpful.

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/5a67e1dc-8c62-4fb5-af45-c158a28563b6

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    • Proposed as answer by Naomi NEditor Thursday, May 6, 2010 6:41 PM
    Tuesday, May 4, 2010 5:25 AM
  • >Codd is the guy who came up with relational database in the first place.

    I know. And his theories are largely irrelevant. What came from his original idea, however, are the databases we know today.

    Studying Codd, i would think, is set aside for database theorists, who have their place in RDBMS-product development, and those who feel a need to argue with those who misquote him.

    >I am just trying to find common ground in the fundamentals so we can converse about database design topics.

    I understand. I was just pointing out that using the term, in itself reflects an attitude.

    The attitude is that design is a step-by-step process. First design TABLEs, then design keys. That defines a logical/deductive process. I use an intuitive/inductive process. I get the basic idea of the entire model and identify objects. The keys and their relationships practically define themselves at this point. If i have to think about a key, i have to question whether i actually defined the object properly.

    Hence, the idea of searching for candidate keys, at least for me, means i did a bad job at design, and probably need to start over again.

    >It is like studying physics but having never heard of Newton.  Sure his principles have been refined over the year by people like
    >Einstein, but Newton is the father of physics, and Codd is the same to Relational databases.

    True. And i've heard of Codd. But just like none studies Newton, noone studies Codd.

    Tuesday, May 4, 2010 11:40 AM
    Answerer
  • >>Studying Codd, i would think, is set aside for database theorists, who have their place in RDBMS-product development, and those who feel a need to argue with those who misquote him.<< >>And his theories are largely irrelevant. << >> But just like none studies Newton, noone studies Codd<<

    Um, everyone who is a physicist I would imagine still studies Newton's three laws of Motion, just like data architects should understand Codd's 12 Rules, which outline the basic principles of relational databases, plus the normalization rules that he was the starter of.  And to not know wthe foundation of the science that you work with is dangerous.  Some things that seem silly out of context are very useful out of context.

    >>The attitude is that design is a step-by-step process. First design TABLEs, then design keys.<<

    I never teach this to anyone, personally.  I teach people to follow the natural design process that is very intuitive, but applying the principals of normalization along the way. And when you are done, and think you have arrived, having rules to check against certainly doesn't hut.   Admittedly, when you teach someone the basics, you do have to teach them what a table is before a key, but you have to start somewhere.

    In any case, we should move along and agree to disagree on some points.  I  don't think that we will ever agree completely


    Louis

    Thursday, May 6, 2010 5:04 PM
  • > I  don't think that we will ever agree completely

    I agree!!

    /me implodes due to paradox.

    Thursday, May 6, 2010 5:17 PM
    Answerer
  • The genius of Codd is the creation of Relational Algebra.

    We are used to addition, subtraction, etc. for numbers as objects.

    Codd extrapolated the concept of objects to tables which may have several columns and millions of rows. Basic operations are set-based.

    An operation  reminiscent of creating a subset:

    SELECT * INTO ProdOver800 FROM AdventureWorks2008.Production.Product 
    WHERE ProductID > 800

    creates a new table (or results ) from an existing table.

    The new table is as "good" as the original, it can be subjected to the same kind of operations.


    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    Thursday, May 6, 2010 5:30 PM
  • That could be.

    When i learnt databases, i didn't hear of Codd. I learnt to design, without Codd. And i give advice to others, and do not mention Codd.

    It just comes naturally to me. No algebra. No rules. No worksheets.

    The more i hear about Codd-bearing individuals, and how Codd's rules must not be broken, the more i think it is a logical approach to define the data, and the model itself happens by accident. I believe, however, the intelligence is in the design, and the objects evolve from there.

    ok, i'll stop now. It's beginning to hurt.

    Thursday, May 6, 2010 5:40 PM
    Answerer