Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2002
    Posts
    10

    Question Doubt in DB design. Please Help.

    In some situations especially in smaller tables, I am forced to have the entire table as primary Key. This is because I want the entire record combination to be Unique. This happens especially in Catlogues / Master tables.

    Is this a wrong practice. If so How do I overcome this?


    Can I keep Timestamp / Date/ Time as a part of primary Key. Are there any performance Over heads?

    Thanks & Regards
    M.Prakash

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you have a catalogue or master table that requires all columns to be in the primary key, this would be an extremely unusual situation

    surely there is a product code or some other natural key that will distinguish the items in the catalogue

    as for a datetime value in the primary key, no, there is no overhead

    but if you do have a datetime value in the primary key, chances are you will not need any other columns in the primary key, unless your application can accept and process multiple new entries per millisecond


    rudy

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Re: Doubt in DB design. Please Help.

    Originally posted by muthuprakash99
    In some situations especially in smaller tables, I am forced to have the entire table as primary Key. This is because I want the entire record combination to be Unique. This happens especially in Catlogues / Master tables.

    Is this a wrong practice. If so How do I overcome this?
    There's nothing wrong with that, except that you might find queries become hard to express in SQL.

    If you had a table like this:

    Lastname, Firstname, DOB

    ... where all columns are part of the primary key, you could just create a view that added an extra column like so:

    candidateKey = Lastname & "_" & Firstname & "_" & cast(DOB to VARCHAR)

    So now you have an extra candidate key, and you can use the view for joins and so forth.

    if you have a catalogue or master table that requires all columns to be in the primary key, this would be an extremely unusual situation
    This is flat out wrong since he explicitly says he wants the "entire record combination to be unique."

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    flat out wrong?

    i said it would be extremely unusual for a catalogue or master table to have all columns as part of the primary key

    perhaps, sco08y, you could give me a real-life master table example (not a made up example), that has all columns as part of the primary key, and explain how it is commonplace, usual, or typical

    even then, i won't be flat out wrong, i'll just have to moderate it down from extremely unusual to highly unusual

    perhaps the misunderstanding comes from our interpretation of a catalogue or master table?

    i just can't see amazon's book master table or sears roebuck's catalogue table having all columns as primary key


    rudy

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Originally posted by r937
    flat out wrong?

    i said it would be extremely unusual for a catalogue or master table to have all columns as part of the primary key
    You're wrong because he explicitly said the table has all columns in the primary key, unless you were suggesting he was mistaken to do so, which might be the case.

    perhaps the misunderstanding comes from our interpretation of a catalogue or master table?
    All I know of are base tables and derived tables. I don't see how any table can be considered the "master" in a normalized schema.

    perhaps, sco08y, you could give me a real-life master table example (not a made up example), that has all columns as part of the primary key, and explain how it is commonplace, usual, or typical
    Code:
    CREATE TABLE FIGURE_ARTICLE_LINKS (
    	figureId	INT	FOREIGN KEY FIGURES,
    	articleId	INT	FOREIGN KEY ARTICLES,
    	PRIMARY KEY (figureId, articleId)
    )
    I think many to many joins qualify as commonplace.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks sco08y

    i did not say he was wrong to put all the columns in the primary key

    i said a catalogue or master table with all columns in the primary key would be unusual

    you obviously do not know what a catalogue or master table is

    a many-to-many junction table is not a catalogue or master table

    but thanks for trying


    rudy

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by sco08y
    All I know of are base tables and derived tables. I don't see how any table can be considered the "master" in a normalized schema.
    I would call ORDER the "master" table in the relationship:

    ORDER--<ORDER_LINE.

    The ORDER is a master in the sense that it does not depend on another table for its existence, unlike ORDER_LINE. I believe Rudy is using the term in that (well-known) manner. It is not a normalisation term, but it does have meaning.

    Originally posted by sco08y

    Code:
    CREATE TABLE FIGURE_ARTICLE_LINKS (
    	figureId	INT	FOREIGN KEY FIGURES,
    	articleId	INT	FOREIGN KEY ARTICLES,
    	PRIMARY KEY (figureId, articleId)
    )
    I think many to many joins qualify as commonplace.
    Yes, but they don't qualify as master tables, as by definition they only exist to link together 2 other tables. Can you think of a sensible master table with all the columns in the primary key? It seems to me that this can only happen with tables that represent a domain, like:

    CREATE TABLE video_category( vc_name VARCHAR2(20) PRIMARY KEY );
    INSERT INTO video_category( vc_name ) VALUES ( 'Drama' );
    INSERT INTO video_category( vc_name ) VALUES ( 'Comedy' );
    INSERT INTO video_category( vc_name ) VALUES ( 'Documentary' );

    Maybe this is the sort of "picklist" table the original questioner had in mind?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thank you, tony

    now, suppose that for Video Category, in addition to Name, you also wanted DateAdded, AddedByUserid, Description...

    if those were all part of the primary key, i'd say that would be extremely unusual

  9. #9
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    So long as we're not telling the original poster "you can't do that!" I've made my point.

    (Edit: And if add a "AddedbyUserID" field, it's not a master table anymore, which shows that the concept is rather useless.)
    Last edited by sco08y; 03-06-03 at 12:20.

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by sco08y
    So long as we're not telling the original poster "you can't do that!" I've made my point.

    (Edit: And if add a "AddedbyUserID" field, it's not a master table anymore, which shows that the concept is rather useless.)
    What? Why isn't it a master table any more? Admittedly, the term "master table" is perhaps not a rigorous definition, but most database designers would know what was meant by it. Perhaps a viable definition would be: a table whose primary key does not contain columns that are foreign keys to some other table. So Employee, Department, Product and Order would be master tables, but Emp_Dept, Emp_Salary_History and Order_Line would NOT be.

    Some analysts user the terms "strong entity" and "weak entity" to mean roughly the same thing.

    Now, the original question mentioned "Master" tables. Assuming he meant the term as I have just described it: can you come up with an example of a "Master" table with more than one column, where all the columns are in the primary key? (I think we can all agree that a Master table with only one column MUST have all its columns in the primary key!)

    I think we are probably all agreed really that tables CAN have all their columns in the primary key (intersection tables, one-column lookup tables), but that a "master" table with several columns, all in the primary key would be rather suspect. Yes?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rather suspect? extremely unusual, yes



    my concept of a "master" table goes back to mainframe systems, but i hasten to mention that i strive not to let my rich mainframe background cloud my thinking in this age of the internet

    an example of a "master" table is a products table, keeping track of all the data about a company's products -- this is also how i understood "catalogue" table

    by comparison, a "detail" table would be the records that need to be processed against the master, such as individual sales transactions, that would affect product inventory levels

    no way do all product columns go into the product primary key


    rudy

  12. #12
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Tony, when you said "doesn't depend on another table for its existence" you suggested (at least to my mind) that a master table is one that doesn't have any foreign keys.

    Your second definition is better.

    but that a "master" table with several columns, all in the primary key would be rather suspect. Yes?
    Well, by the time your system is up and running, yes, someone will have added a few columns. The original poster was clearly in the design phase, and I've designed a number of all-primary-key tables that later had a few useful columns added.

    One other problem is that it's can be a PITA to work with composite keys in SQL so we often avoid them. Again, I'd argue that when you're designing you shouldn't make assumptions like "composite pk's are bad."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •