Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2010
    Posts
    17

    Unanswered: one to many relationship

    I am creating a database to track inventory
    I have my main assets table (tblassets) and then an operating system table (tblOS). i uses the OS table to populate a combo box on the assets table.

    would a one to many relationship be correct?

    The main problem i am seeing is that if i want to skip choosing an OS i get an error that "no related record on tblOS"

  2. #2
    Join Date
    Apr 2010
    Posts
    17
    posted screenshot
    Attached Thumbnails Attached Thumbnails untitled.JPG  

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes. You will. The way you have it setup now, you need a value in the tblOS. The advantage to this (if you click on the joining line and set cascade update to true), is that you can then change the OSID and it will cascade that change throughout the tblAssets table.

    Otherwise, you'll need to redo your relationships or structure if that's not what you want (ie. perhaps the join should go the other way.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Apr 2010
    Posts
    17
    So is the way I have it setup the correct way to do it?

    Only problem I can think of is that I have Assets that do not require operating systems (ex phones).

    would it best to build the DB in a way where i can still have a combo box list based off another table and not be required to fill in the field

    or make a field option to be "N/A" on the OSid table

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your relationship is the wrong way round. The OS is an (optional) attribute of the asset. All your OS table does is provide a list of valid values for that column. As such, the OS table is not even necessarily required, just like the asset_type table.

    Additionally, a sup-type super-type model is well suited for modelling the asset since an asset can be one of many different physical items each requiring different columns. If you are familiar with OOP then the supertype is like an abstract base class and the subtypes are inheriting classes. http://www.cbe.wwu.edu/misclasses/mi...persubtype.ppt

  6. #6
    Join Date
    Apr 2010
    Posts
    17
    Thanks for posting that. Now i am beginning to think my DB is all wrong.

    I have read that it is good to list combo boxes off of tables so that is why I built them. To tell you the truth i never really understood why? i assumed it was easier to edit. is that correct?
    if not think it would be better to get rid of them?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - I said they were optional. That doesn't mean you should not use them!

    Since the OS and asset_type tables do not contain any non-key columns (each contains a natural key and a surrogate key) they are not really entities as such. They would not be modelled in a logical diagram. They are not required by relational principles nor by normalisation rules.

    However, they are often useful when implementing a model. The two main reasons are:
    1) To enforce a limited set of allowable values for a given column(s), and to allow those values to be easily changed
    2) To provide a list of limited set of allowable values to be presented to the user for ease of selection.

    You don't strictly require these tables but they are likely very useful for your users and they prevent any typos or invalid entries in the asset table columns.

    The alternatives to not having the tables are:
    1) No restrictions at all on what people can enter (results in more laboured data entry and eventually someone will make an error)
    2) CHECK constraints on the column (prevents users entering invalid data but you cannot present the list to the user and it is a pain to change the list)

    Does that help?

  8. #8
    Join Date
    Apr 2010
    Posts
    17
    it does.
    i am new to databases, let alone building one from scratch. it was mentioned earlier that my relationship was the wrong way around, i assume that means the arrow is point in the wrong direction. But i have not found a way to flip that around.

    what i need to rebuild the relationship? if so what exactly do i need to do?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I beg your pardon - your relationship is fine.

    A relationship has a cardinality. In this case 1 to many. The 1 and the infinitycharacter show the cardinality.

    The arrow is proprietary to Access. When you create queries then the Access will automatically create joins based on the relationships. The arrow (or lack of one) indicates the default type of join it uses when it creates one. Your default join would be better pointing the other way round.

    Apologies - my mistake. I mistook the arrow to indicate the cardinality of the relationship. Everything else we've discussed still applies.

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    To change the direction of your relationship between the 2 tables, double-click on the line joining the 2 tables. You will then get a popup where you can click the 'join type' button and change it to one of the 3 options (ie. only include rows where the joined fields from both tables are equal, Include all records from tableX and only those records from tabley....or include all records from tabley and only those records from tablex where the joined fields are equal.)

    Relationships are very important when setting up your table structure so don't take them lightly. Also note the 'Cascade Update' and 'Cascade Delete' checkboxes. I would experiment (on a test db) with the different kinds of joins and how the data works with the joins. If relationships are overlooked or setup incorrectly, you can end up with 'orphaned' records which basically means more work for you in fixing it (not to mention inaccurate totals).

    You may also want to run the query 'wizard' (the unmatched query wizard) to check for orphaned records. You can't setup relationships between tables if there are unmatched records.

    As far as one way being correct and another being wrong, that depends on what you want to accomplish. Your relationship could be correct if you want it to work that way. Again, experiment with sample tables until you fully understand this important concept.

    You may also want to look at other designs to see different ways relationships are setup. The Northwind.mdb that ships with MSAccess is a good example of how different relationships can be setup.
    Last edited by pkstormy; 04-07-10 at 19:58.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Note that the "join type" actually has no effect on the relationship, and is not even related (pun intended) to the relationship. It is associated only with the joins Access will create for you.

Posting Permissions

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