locked
Database design question RRS feed

  • Question

  • Hi, my database has two tables:

    Scholarships   and Courses,

    these are their columns:

     

    Scholarships:-------------------------

    contry

    university

    organization

    scholarship

    duration

    observations

    requisites

     

    Courses:-----------------------------

    courseName

    country

    organization

    duration

    language

    requisites

    benefits

     

    should each of the tables need an id  column?

    and as i see it, they are not related, so they dont need a foreign key right?

     

    thanks ...

    Thursday, October 7, 2010 9:50 PM

Answers

  • I believe it would be nice to have an ID for each table for the future reference. ID could be TinyInt, SmallInt, Int, or Char as well. 

    For the relationship it depends on your scenario. Explain us what do you want to get and show in the output. So, it would be much easier to say. 

     

    Thanks,


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    • Proposed as answer by SandeepM_ Friday, October 8, 2010 5:21 AM
    • Marked as answer by pyram07 Friday, October 8, 2010 6:28 AM
    Thursday, October 7, 2010 10:34 PM
  • Hi,

       It is good practice to have a primary key for each table ... and also , it is good to identify the relations or dependencies among the tables and create foreign key to avoid data conisstance problems ... So it is always good practice to follow the integrity constraints WHEREVER APPLICABLE::

    1. Domain Integrity -- Check 

    2. Entity Integrity --  Primary Key

    3. Referential Integrity-- Foreign Key 


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    • Proposed as answer by SandeepM_ Friday, October 8, 2010 5:21 AM
    • Marked as answer by pyram07 Friday, October 8, 2010 6:28 AM
    Friday, October 8, 2010 12:33 AM
  • should each of the tables need an id  column?

    and as i see it, they are not related, so they dont need a foreign key right?

    Yes, to make each and every row uniquely identified and best practice to have Primary key

    Even the relation is not there, you can create the index on ID column which gives you faster retrieving data.

    I prefer surrogate keys in this:

    Benefits

    1.      Business Logic is not in the keys.

    2.      Small Size (4-byte key, 8 bytes).

    3.      Joins are very fast.

    4.      No locking contentions because of unique constraint (this refers to the waits that get developed when two sessions are trying to insert the same unique business key) as the surrogates get generated by the DB and are cached – very scalable.

    thanks


    - M S (We are what we repeatedly do, Excellence, then, is not an act, but a habit.)
    • Marked as answer by pyram07 Friday, October 8, 2010 6:28 AM
    Friday, October 8, 2010 5:20 AM

All replies

  • I believe it would be nice to have an ID for each table for the future reference. ID could be TinyInt, SmallInt, Int, or Char as well. 

    For the relationship it depends on your scenario. Explain us what do you want to get and show in the output. So, it would be much easier to say. 

     

    Thanks,


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    • Proposed as answer by SandeepM_ Friday, October 8, 2010 5:21 AM
    • Marked as answer by pyram07 Friday, October 8, 2010 6:28 AM
    Thursday, October 7, 2010 10:34 PM
  • Hi,

       It is good practice to have a primary key for each table ... and also , it is good to identify the relations or dependencies among the tables and create foreign key to avoid data conisstance problems ... So it is always good practice to follow the integrity constraints WHEREVER APPLICABLE::

    1. Domain Integrity -- Check 

    2. Entity Integrity --  Primary Key

    3. Referential Integrity-- Foreign Key 


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    • Proposed as answer by SandeepM_ Friday, October 8, 2010 5:21 AM
    • Marked as answer by pyram07 Friday, October 8, 2010 6:28 AM
    Friday, October 8, 2010 12:33 AM
  • should each of the tables need an id  column?

    and as i see it, they are not related, so they dont need a foreign key right?

    Yes, to make each and every row uniquely identified and best practice to have Primary key

    Even the relation is not there, you can create the index on ID column which gives you faster retrieving data.

    I prefer surrogate keys in this:

    Benefits

    1.      Business Logic is not in the keys.

    2.      Small Size (4-byte key, 8 bytes).

    3.      Joins are very fast.

    4.      No locking contentions because of unique constraint (this refers to the waits that get developed when two sessions are trying to insert the same unique business key) as the surrogates get generated by the DB and are cached – very scalable.

    thanks


    - M S (We are what we repeatedly do, Excellence, then, is not an act, but a habit.)
    • Marked as answer by pyram07 Friday, October 8, 2010 6:28 AM
    Friday, October 8, 2010 5:20 AM