SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: designing db

  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    designing db

    Hello,

    I have to design a database for an office. They have division and section.

    I thought of making 2 tables, division and section.

    table division
    division_id
    division_name

    table section
    section_id
    section_name
    division_id

    section_id is primary key and auto increment in section_table.
    division_id is primary key and auto increment in division_table.

    I want to know which is better here, should i have to remove division_id from division table and section_id from section table and put division_name instead of division_id, since division_name and section_name are unique?

    db is mysql and language is PHP.

    Thanks

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,146
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    In most cases I prefer to play it safe and use a surrogate key, rather than synthetic. I generally only use synthetic keys with tables that essentially hold "enum" values or values that are not manipulated nor referenced individually by a user. I would probably prefer to leave the auto increments in tact here. Data is always easier to reference, especially via URL using an integer. Will section and division have similar data or not really?
    The only code I hate more than my own is everyone else's.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by shivam0101 View Post
    should i have to remove division_id from division table and section_id from section table and put division_name instead of division_id
    should you ? not necessarily

    could you? absolutely, yes

    a lot depends on the size of the actual names in use

    there's no problem avoiding surrogate keys if your division names are like 'Accounting' and your section names are like 'Bean Counting'

    however, if your division names are like 'Corporate Finance and Investments' and the section names are equally long and unwieldy, then you might want to use ids

    another thing to consider is how often (i.e. in how many tables) there will be foreign key references to divisons and sections
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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