Results 1 to 9 of 9
  1. #1
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50

    dB design for Construction Project

    I am a total Nerd, a rank beginner, so apologies for that to start with.
    I am working on an Access app for a real estate project and have thought of laying out the tables in sequence as events would normally happen.
    After the designs have been drawn up and approved we would have Inventory (table 01Units) to begin with.
    As the project develops, intending purchasers would make enquiries (table 02Enquiries) and their details would be stored in table 03Prospects
    Once they finalize a purchase that data would go in table 04Bookings. At this point the prospect has become a customer. Should a table be introduced here.
    Once a booking is made, Bills will be raised as per work progress (table 05Billing)


    As Bills are raised, payments will be made (probably in parts) and go into table 06Payments
    Once payments are received, receipts will be issued and data entered into 07Receipts
    Prospects/Customers are usually brought in by Brokers, whose data will be entered into another table 08Brokers. Thill table will have 2 FKs which relate it to 02Enquiries and/or 03Prospects or 04Bookings

    A schematic pdf is attached to make things clear. Am I on the right track, or some changes are suggested. All comments are welcome.dB Design.pdf

  2. #2
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50
    Sorry, the pdf attachment may not be approved, so attaching an image here

    Click image for larger version. 

Name:	dB Design_001.png 
Views:	40 
Size:	118.9 KB 
ID:	49104

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If those are your real table names, never start any name with a number.
    (table 02Enquiries) and their details would be stored in table 03Prospects - related tables should be parent/child type of relations. I venture to say that enquiries are not related to units/buildings. In that fashion, you cannot have an enquiry unless you can associate a unit with the person. I don't see a need for tblenquiries.

    you need both payments and receipts? Will people make multiple payments for a single billing?

    if a prospect can have many bookings, you need a junction table for that
    IMO, notes belong in their own table(s) IF you will be appending notes.

    Type is a reserved word - http://allenbrowne.com/AppIssueBadWord.html
    phones makes me think you'll use multi-value fields (not advised). tblContacts would be better. Link tblBroker.BrokerIDpk to tblContacts.BrokerIDfk. Note the pk/fk suffix - not a bad way of always knowing which is which.

    All in all, a pretty good start. Looks like you have some db design experience or at least did some homework first.
    PS - pics of relationships/forms etc. are usually preferred over pdf attachments for same, but no pics for code please.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This is the same question/subject?
    https://www.accessforums.net/showthread.php?t=87021
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50
    If those are your real table names, never start any name with a number.
    Ok. Noted.
    I don't see a need for tblenquiries.
    Will check that out.
    if a prospect can have many bookings, you need a junction table for that
    Yes that is likely.
    Will people make multiple payments for a single billing?
    Yes. Most of the time it works like that.
    Type is a reserved word
    Noted. Will change that.
    This is the same question/subject?
    https://www.accessforums.net/showthread.php?t=87021
    It is a question relating to a small part of this design. That question relates to just the first 2 tables.

    Thanks a lot for your inputs Micron. I think we are beginning to go somewhere with this project. I think within a week or so I should have a basic working system in place and then it would progress based on Use case scenarios. Happy to be on this forum.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Then to be able to record multiple payments per billing or multiple bookings per prospect I think you need a junction table for each case.
    I see you've already been called out for multi-value fields
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50
    The table names with numbers were designated for ease of understanding the process flow.
    I think a workaround for table names beginning with numbers would be 01Units to be renamed as tbl01Units ?

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure what everyone else does, but once I figure I have the tables right I would copy the db. I would probably split it next but if not, there would be at least daily backups of the progress. I'll enter some records directly and test relationships. Then I'd see if I can get out (query) what I need, and be able to edit/append with those queries as needed before I ever think about building forms or reports. If everything is good then it's time to split the db copy if it hasn't been done already. I'd build forms in the front end and use those to enter real data. I could import the queries I made into the copy (which now becomes a master of sorts).

    So from that point on, tables are not to be edited/appended directly. Users should only use forms. Once you get to that point, what difference does the table numbering mean since you don't look at them again? If you need a process flow of some kind, I would not use table names to guide me. I'd call Units tblUnits and save myself all the number typing. Besides, tbl prefix is an example of using an object name prefix, which IMO is something you should do for everything.

    http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837
    Last edited by Micron; 11-13-2022 at 11:14 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Sam is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2022
    Posts
    50
    Thanks. Those are very valuable inputs. And thanks for the links as well.

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

Similar Threads

  1. PROJECT MANAGEMENT DATABASE SYSTEM (PMDS) Construction
    By shephardfamilyenterprise in forum Access
    Replies: 6
    Last Post: 08-05-2021, 03:39 PM
  2. Replies: 2
    Last Post: 06-20-2018, 08:31 AM
  3. Construction Heavy Equipment Database Design
    By DatabaseManiac in forum Database Design
    Replies: 9
    Last Post: 02-28-2018, 04:08 PM
  4. Replies: 24
    Last Post: 09-09-2017, 09:38 AM
  5. Replies: 5
    Last Post: 12-08-2015, 01:57 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