Results 1 to 6 of 6
  1. #1
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73

    Post Database Design...a little help needed!

    Hello everyone,



    In my database I have several tables that all require a link to one user table. When laying this out in a relationship diagram it all seems a little messy. My question is, how many links to a single PK field in one table is possible? At present I have over 10 entities that all require a link to the user_ID in the tbl_users.

    Am I doing something incorrectly as this just doesn't seem right?

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    how many links to a single PK field in one table is possible?
    don't think there is a limit as such - but there are limits in queries - which is more to do with the number of indexes. See this link http://www.databasezone.com/techdocs/acclimit.html and look under the query section

    As to whether it is right or not, not possible to say without understanding what your db is required to do, the business rules it needs to follow and how you have structured the tables and relationships to meet those requirements

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Beanie_d83 View Post
    Am I doing something incorrectly as this just doesn't seem right?

    It depends on your database structure - do you really need so many tables? The only questionable point is this. But with info so sparse, we can hardly give more help.

  4. #4
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    We are a vehicle transporter manufacturer and I work within a small team of design engineers using computer aided design. At present, a handwritten paper system is in place to generate part numbers for each type of component, when it was created and by which design engineer.

    The different component types are prefixed (these form the large amount of tables I have) and have their own set of sequential part numbers. The team have requested that the new database picks up the numbering system in the same way as starting from the beginning again with throw out all the pre-existing components that are already in the CAD software. We also need to keep track of any amendments made to the components after the first date of issue.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    As I suspected it looks like you plan to have a table for every type of components! Is it so? When yes, then you are on path to complications in future!

    At start a bit about terminology I'll use - so no misunderstanding happens.
    Article/Part - describes a set of objects with some similar properties;
    Article/Part ID - a numeric identificator od Article/Part in your database. Often an autonumeric field. Is used as primary Key in Articles/Parts table;
    Article/Part number - an identificator of Article/Part in technical or ERP information system. Usually a textnumeric field. Preferably you define an unique index on this field in Articles/Parts table;
    Item - a specific member of Article/Part set. When there is a need to identify items, then they are identified by serial number, which are unique for given Article/Part. when all Articles/Parts always have serial numbers, then you preferably define an unique index on Article ID and Serial number in Items table;
    Serial Number - defines a specific Article/Part in specific set of Article/Part.

    So you need tables:
    tblArticles: ArticleID, ArticleNo, ArticleName, ...;
    tblItems: ItemID, ArticleID, SerialNumber, ...

    As I understood, you want numeric serial numbers for items, where the number is growing by 1 for every added item having same ArticleID. For this, you design 2 forms.
    fArticles will be a single form, where you can select specific article. In this form, you have text boxes txtArticleID (invisible), txtArticleNo, txtArticleName, ... . also you can have an ubound combo box to select an existing article from tArticles, and an OnChange event for this combo to find the selected article and activate it in form;
    fItems will be a continuous form with fields txtItemID (invisible), txtArticleID (invisible), txtSerialNumber (locked), ...;
    Now you open fArticles in Edit mode, and drag fItems into it. A subform fItems is created and automatically linked with fArticles (it's best to rename the subform, p.e. as sfItems, to avoid some confusion further).
    Next step is to write Current event for fItems. The event must check, is the current record a new one, and when yes, then calculate a new serial number for item and write it into txtSerialNumber (NB! Into textbox control, not into table field directly!).

    To calculate a new serial number in fItems Current event, you use either
    1. DMAX() function to calculate the biggest existing serial number for given Article in tItems and add 1 to it;
    2. A query like (the syntax on fly - not tested)
    Code:
    SELECT TOP 1 SerialNumber +1 AS SerialNumber FROM tItems WHERE ItemID = Me.txtItemID ORDER BY SerialNumber DESC
    Now when you open the form fArticles, in subform all items registered for active article are displayed. And whenever you add a new row into sfItems, a new serial number is written into txtSerialNumbers. When then you leave the record (selecting another row in subform, or leaving subform by activating some control in fArticles), the new item is added into tItems. Pressing Esc a couple of times before leaving the record aborts the new item creating.

  6. #6
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    Wow! Thank you for your detailed response, you have pretty much summed up what I am trying to achieve here! I can clearly see now how this method will be a great improvement to the way I was initially going about it!

    I shall give this a try and see how I get on!

    Thanks again!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Expense database design help needed
    By hira_iftikhar in forum Access
    Replies: 2
    Last Post: 02-17-2016, 05:06 PM
  2. Help needed for database / form design
    By zonker in forum Database Design
    Replies: 3
    Last Post: 06-04-2014, 02:24 AM
  3. Database Design - Help needed urgent -New member
    By tripoliguy in forum Database Design
    Replies: 2
    Last Post: 05-09-2014, 01:12 PM
  4. Database design help needed please guide me.
    By ased in forum Database Design
    Replies: 1
    Last Post: 10-01-2013, 04:19 PM
  5. database design help needed
    By princeofdumph in forum Database Design
    Replies: 2
    Last Post: 01-03-2013, 08:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums