Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: Help with basics of database design (was "DataBase Newbie")

    Hi -
    I've been using Access for my website database's for a while now, but have really been scraping my was through.
    I have created a simple database and wonder if you guys could check it out - i've attached a screen grab of it.
    Any advise appreciated.
    Thanks
    Andy
    Attached Thumbnails Attached Thumbnails dbscreengrab.jpg  

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    re

    first thing that jumps in to my eye and realy hurts is :
    the link between Company and instruments.
    Make a new field Company_id and link that 1 to Company

    if you do a join / sql it is much easyer/ quicker.
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Firstly, there is no detail on what you want this database to do. We must assume things and that's not great.

    Guessing
    History table has no primary key.
    Instruments table has no primary key.
    Instruments table should relate to the company table via it's primary key, CompanyID, not company name.
    Enforce the relationships.

    Hope that helps.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Feb 2008
    Posts
    120
    Thanks Marvels
    That makes sense -
    Like This??
    Attached Thumbnails Attached Thumbnails dbscreengrab2.jpg  

  5. #5
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Smile Re

    Thats better !
    But like StarTrekker said there are missing primary keys
    you called them id, but there not jet (key-figure see CompanyID in Company table)

    b.t.w. : What i normaly do is calling the PrimaryKey in table it self : Id and i call the ForeignKey _id so in your case

    Table : Company
    PK : Id

    Table : Instruments
    PK: Id
    FK : Company_id

    Table : History
    PK: Id
    FK : Instruments_id
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Firstly, there is no detail on what you want this database to do. We must assume things and that's not ideal.

    Task List
    * History table has no primary key.
    * Instruments table has no primary key.

    * Instruments table should relate to the company table via it's primary key, CompanyID, not company name. (Done).
    * Enforce the relationships.

    If you need help on how to do these things, just ask if/when you get stuck
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Feb 2008
    Posts
    120
    Thanks again guys -
    I've added the PK to the Instrument and History tables, but left the naming the same. The InstrumentID won't be numericle so i can't use it as an autonumber.
    Does this look correct now?
    Andy
    Attached Thumbnails Attached Thumbnails dbscreengrab3.jpg  

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Task List
    * History table has no primary key. (Done).
    * Instruments table has no primary key. (Done).
    * Instruments table should relate to the company table via it's primary key, CompanyID, not company name. (Done).

    * Enforce the relationships.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Feb 2008
    Posts
    120
    Really appreciate your help guys - thanks
    How do i enforce the relationships?

    Andy

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Double click on the line and check (set) the Enforce Referential Integrity and Cascade Update Related Fields checkboxes.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Marvels
    b.t.w. : What i normaly do is calling the PrimaryKey in table it self : Id and i call the ForeignKey _id so in your case

    Table : Company
    PK : Id

    Table : Instruments
    PK: Id
    FK : Company_id

    Table : History
    PK: Id
    FK : Instruments_id
    Just a quite picky note...
    Don't use this naming convention

    It's quite acceptable to have a company_id field in the company table!
    "ID" is so bland; it doesn't tell us very much about the field - it is one of the only exceptions where you are allowed to prefix a field name with the table name!

    A great benefit of this is that it makes your query joins a lot easier to write (and remember) (order_id links to order_id; fancy that!)
    George
    Home | Blog

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    balderdash and piffle

    please do go ahead and name the id of each table "id"

    the great benefit of this is that it makes your query joins a lot easier to write

    instead of orderitems.order_id = orders.order_id you will say orderitems.order_id = orders.id

    the benefit of this is that you will know at a glance which one is the PK and which one is the FK

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Each to their own I suppose. I am a big fan of table aliasing, so I rarely find myself in the position of writing the fully 2 part name. Either way, I chose to name the primary key in such a way as it describes the data. I would argue that neither of us are incorrect in our method or justification

    Oh and...
    Quote Originally Posted by r937
    balderdash and piffle
    QOTW!
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    I am a big fan of table aliasing, so I rarely find myself in the position of writing the fully 2 part name.
    whoa, what did you say?

    it sounds like you name all the columns in the orders table order_id, order_date, order_whatever...

    please say it isn't so
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    No, no, no, no, no! Give me some credit Rudy!

    Only on a primary identity field (surrogate?).
    George
    Home | Blog

Posting Permissions

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