Results 1 to 3 of 3

Thread: Relationships

  1. #1
    Join Date
    Oct 2012
    Posts
    20
    Provided Answers: 3

    Answered: Relationships

    Hello,

    I am a database newbie, we are making a database to track housing maintenance. We would like the database to be able to generate a report with upcoming maintenance items due based off of the Unit Number. I have created 5 tables with relationships as follows;

    Click image for larger version. 

Name:	DBRelationships.PNG 
Views:	14 
Size:	20.2 KB 
ID:	16598


    How do it get it working correctly? Please let me know if you require more information. Thanks in advance for your help...


    Dave

  2. Best Answer
    Posted by DFeil

    "Cheers, thanks for the info, I will try again..."


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't think you have grasped the details of table design and relationships. Each table needs a unique key, which (in the absence of an obvious better item(s) can be an autonumber value). as a general rule don't repeat data (google nomalisation)
    in a relational database you can have several types of relationships, but in essence its usually a 'child' table should have the primary key each/any 'parent' table as a foreign key in its own table. Think of it as (parent) table has one or more (child) table rows. ferinstance:-
    you may have inspectors, who perform inspections on equipment. so at first glance you could elect to have the primary key of the inspector as a foreign key in equipment storing the last inspector of that equipment. However if you needed to know the inspection history of the equipment you need a third table (an intersection table [google intersection table]) that identifies what equipment was inspected by which inspector AND any other dtaa that was relevant to that intersection. lets call this table inspections and its primary key could be the date of the inspection, the equipment id and the inspector id). but if you had more than one inspector on the same job then you need to refine that design to allow for that. if more than one equipment is inspected as part of the same inspection you need to design for that. in that latter case you could have equipment and inspector tables, then a table for inspections (detailing when an inspection occurred), a child table off that idenitfying which inspector(s) were part of that inspection, and another child tabel identifying which equipment was inspected on that date

    each table should be a discrete 'pool' of data, but there is no need to do things such as have a table of dates

    based oin your design it looks like you don't understand the concept of primary and foreign keys (google primary and foreign keys). as on the face of it you are using an autonumber key for each table and trying to use the same autonumber id for each table. in a realtional db the whole principal is that you use realitonal links, although Accerss allows you to use non relational links generally you should resist the temptation to do so. for realtional links think of it as a way of constrainign the data to make certain it all stacks up.

    ...ferisntance:-
    you have a table for products...
    ...it has a primary key (called say product_id)
    you have a table for customer orders...
    ...it has a primary key (called say order_no)
    and off those you have a table for order details (essentially an intersection table identifying what products have been ordered
    ...it coudl have a primary key of order_no AND product_ID.. that way round a customer cannot order the same product more than once in the same order (if they need to do so then you need a quantity_ordered column in the order details table to handle that. you could use an autonumber column for the order details table (and thats what Access table designer will suggest) but I'd argue that is the wrong way to do it. by making the p[rimary key of the order details table order number and declkared it as a referential (RI) relationship product id you have enforced that before you enter a product in an customer order ther emus pre exist an order number and a product. on toip of that the order table should have a foreign key pointing to customer (customers can place one or more orders, each order must comprise one or more products). if a customer can only ever order one product then arguably you don't need the order details table.

    so to reprise
    read up on normalisation
    read up on primary and foreign keys, and relational table design
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Oct 2012
    Posts
    20
    Provided Answers: 3
    Cheers, thanks for the info, I will try again...

Posting Permissions

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