Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Sep 2002
    Posts
    454
    Thanks
    0
    Thanked 20 Times in 20 Posts

    quick database question

    Tables do not always have to have and id column and primary_key right?
    ex:

    id int NOT NULL AUTO_INCREMENT,
    title varchar(200) not null,
    desc longtext not null
    PRIMARY KEY(id)

    As long as there is an identifier of who the row belongs too?
    title varchar(255) NOT NULL,
    desc longtext NOT NULL,
    parent_id int NOT NULL

    right?
    NO Limits!! DHCreationStation.com
    ------------------------------------------------------------
    Broken items wanted for tinkerin'! PostItNow@BrokenEquipment.com
    Global Complaint Dept.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Without a primary key that indicates that duplicates are allowed which means you can never accurately target a unique record.
    Properly normalized, there should always be a primary key. A surrogate id is a convenience that's often treated as the determining unique factor. This can drag a designer into a false sense of normalization.
    In the above you have, if the parent_id is unique, that would then become your primary key. If it is not unique, I'd suggest that the parent_id and title are unique making it a unique composite.

    Also, this has nothing to do with PHP. Moving to other databases forum.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    Regular Coder
    Join Date
    Sep 2002
    Posts
    454
    Thanks
    0
    Thanked 20 Times in 20 Posts
    Thanks for the input...

    I have the db setup to where I get a main listing.
    each of the items has a unique id.
    I figured I since I would be identifying the the settings based on the unique id of in the main listing I could remove the extra columns. save coding.

    example: main listing
    id | site title
    1 | my site
    2 | your site


    page
    heading | parentid
    welcome to my site | 1
    thanks for visitng my site | 2

    sort of like a tier construction model then all changes would be base on main sites unique id
    NO Limits!! DHCreationStation.com
    ------------------------------------------------------------
    Broken items wanted for tinkerin'! PostItNow@BrokenEquipment.com
    Global Complaint Dept.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,208
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    The question is: Can there be multiple occurrences of the same parentid??

    As in:
    Code:
    heading                    | parentid
    welcome to my site         | 1
    thanks for visitng my site | 2
    zambonis rule              | 2
    If not--if parentid must be unique in the table--then you have a 1-to-1 table and one has to wonder why that table exists in the first place.

    But in any case, there is *NEVER* any requirement that a table have *ANY* kind of key, unique, primary, non-unique, or whatever. Keys are *ONLY* needed for improving performance.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Sep 2002
    Posts
    454
    Thanks
    0
    Thanked 20 Times in 20 Posts
    Thanks Old Pedant...
    The question is: Can there be multiple occurrences of the same parentid??
    Yup that is the leading question! I just found that out the hard way! With multiple occurances of the parentid you *Need* a unique identifier to make editing easier. In other tables though the parentid becomes the unique identifier when only one entry is allowed per parent.

    Thanks for the help!
    NO Limits!! DHCreationStation.com
    ------------------------------------------------------------
    Broken items wanted for tinkerin'! PostItNow@BrokenEquipment.com
    Global Complaint Dept.

  • #6
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,631
    Thanks
    0
    Thanked 648 Times in 638 Posts
    Where a table has a many to one relationship to another table the primary key will typically consist of two fields - the first being the key of the record in the other table and the second allowing the records associated with the other table to be uniquely identified. That's assuming that you have a primary key and that duplicates are not allowed. If duplicates are allowed in the many table then both tables can use the same key.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.


  •  

    Posting Permissions

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