Page 1 of 4 1234 LastLast
Results 1 to 15 of 48
  1. #1
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163

    Relationships

    I just started this database. All I've done is create the tables and am setting up the relationships. I was toying with the idea of including CustID in the tblinvoice table but I'm thinking there is already an indirect link through tbltick, tblEqup. In fact, that is my question. Am I ok as long as I can follow a path back? Seems redundant to have two paths there. Thanks.




    Click image for larger version. 

Name:	relationsip.png 
Views:	50 
Size:	37.8 KB 
ID:	44252

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I would disambiguate your primary keys to be more descriptive. CustID, EquipID,TickID,etc. If you want to disambiguate your foreign keys then name them EquipFK, TickFK, etc.

    I would include CustID in the tblinvoice but I'm not totally confident I understand your relationships like with tblEquip, tblpartOrder and tblParts.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    We don't know the business so can only guess. If you can have an invoice without a tick, then you have a problem I'd say. Thing to do now is enter test data and see if you can query, edit and append with that design. Another example would be, is that a table for parts you stock? Then you may not be able to add a part stock number without an order.

    Type is a reserved word. Really shouldn't use it, and I agree with the notion of ambiguous ID names. It was the first thing I noticed.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Quote Originally Posted by Micron View Post
    We don't know the business so can only guess. If you can have an invoice without a tick, then you have a problem I'd say. Thing to do now is enter test data and see if you can query, edit and append with that design. Another example would be, is that a table for parts you stock? Then you may not be able to add a part stock number without an order.

    Type is a reserved word. Really shouldn't use it, and I agree with the notion of ambiguous ID names. It was the first thing I noticed.
    In my mind, you cannot have an invoice without a ticket. But I'll think about that some more. This is a service type business so regarding the equip-partorder-parts relationship...A customer will have equipment to be serviced, a service ticket is created and parts are ordered.

    But then I was thinking that there could be a situation where another set of parts needs to be ordered for the same ticket. I thought there would be some value in having that flexibility there for the future, but I can see the problems with the way that's set up now that you mention it. With at least the way the fields are.

    I'll clean up the foreign key names as well. Thank you.

    It's funny how this works. In my head I am so confident that a situation is not going to occur until someone asks if it will...and then all of the sudden it seems possible.

    If we were to determine that there were not going to be any invoices without tickets, or tickets without customers, is there any value to including the CustFK in tblIinvoice? Is it a better idea to have tblCustomers at the top of the food chain with as many tables as possible with CustFK?

    I seemed to have set it up in series vs parallel. It just seemed that I wouldn't have a customer without equipment and I wouldn't have the equipment if it didn't need a repair ticket. So why clutter up the relationship table if I can find my way back through all of the tables if necessary.

    My gut tells me that having tblCustomers at the top with CustFK running around like bunnies would be best because it may offer more tolerance for change down the road? Also, the customer has the money.

    I don't know...my head is about to explode. I built a similar database last week as I was refreshing my memory and ran some test data through it. It worked great, which scared me. Of course, I thought I could do better so I decided to start over.




    Thanks

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You should be able to have multiple part orders with that schema. Not sure I agree with Paid fields in parts or parts orders. They probably belong in a PO, POlineItems or Invoicing table.
    is there any value to including the CustFK in tblIinvoice?
    I'd say that would be an example of repeating data in other tables, so no.
    One thing you cannot do with that design is invoice more than once for a ticket - so no partial invoicing allowed. Nor could you cancel an invoice, keep the record and have a do-over. 1 to 1 relationships are seldom a good idea as they usually mean each half of the join really should be together. In this case, that doesn't seem to apply, but those limitations might.

    This is where you really have to understand the business process, which usually means asking 'what if' many times. The end user seldom thinks of everything you really need to know. Sometimes you cannot ask specific questions because you don't know what you don't know. If the customer told me "Next we create the invoice...." and I've asked my specific questions, I'd also be asking "What issues have you experienced with this part of the process?" hoping we uncover that multi invoice issue. That is an example of a leading question.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Quote Originally Posted by Micron View Post
    You should be able to have multiple part orders with that schema. Not sure I agree with Paid fields in parts or parts orders. They probably belong in a PO, POlineItems or Invoicing table.

    I can rework that. I have a partner and sometimes he'll buy parts and sometimes I'll buy parts. I want to be able to track it. Maybe that's not the best place for it. When you say POlineItems, are you talking about a many-to-many? I have to confess... I think I have this set up backwards now that I look at it. I don't have a parts table. The parts in tblParts are manually entered with each ticket. Do you think it would be smart to have a parts table from which to add to the tblPartOrder?




    Quote Originally Posted by Micron View Post
    One thing you cannot do with that design is invoice more than once for a ticket - so no partial invoicing allowed. Nor could you cancel an invoice, keep the record and have a do-over. 1 to 1 relationships are seldom a good idea as they usually mean each half of the join really should be together. In this case, that doesn't seem to apply, but those limitations might.

    Right now I'm a one-man operation with a part-time partner for some projects. My initial thought was only one invoice per ticket. I indexed the TicketFK with no duplicates to prevent double invoicing. But the more we discuss it, I realize I'm limiting the scalability of the platform. I thought I would just code some delete procedure to take care of any invoice mistakes but I can see how you might not want to program in that ability to someone else if it came to that down the road.


    But I racked my brain for a while trying to think of a scenario where I would want more than 1 invoice for a ticket and I can't come up with another one...now. My invoices are relatively small as the equipment is relatively inexpensive. Lawn mowers and related equipment. But that doesn't mean I can't design it just in case. Go big or go home, right? Who knows, maybe that would change though.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    When you say POlineItems, are you talking about a many-to-many?
    That would be one PO to many line items on that PO
    Do you think it would be smart to have a parts table from which to add to the tblPartOrder?
    If those are parts from your inventory, yes. I couldn't tell if the part was what the customer gave you to service or what.
    What if the invoice is incorrect, disputed or otherwise amended? You will have to resubmit based on the existing record and lose potential useful data. You could not resubmit an amended invoice with a new ID for the same ticket, e.g. first is 01245, second would be 01245-B. Nor could you partial ship and partial invoice. If customer gives you 4 identical items to fix (4 lawn mowers?) I presume that's one ticket. Are you going to hold the entire order until all are completed? Or do you create 4 tickets for 4 items from the same customer, possibly quadrupling your data input just so that you can multi-invoice and not hold up the return of even one item because you can't partial invoice?

    These are situations you have to envision and decide how to handle them. I see no benefit to a design that precludes being able to do something that was thought of if it makes little difference to the effort needed to provide the capability. Things evolve and what seems improbably today has a way of creeping in to the requirements.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    You should take a look around Barry Williams' site http://www.databaseanswers.org/data_models/index.htm
    He has hundreds of data models like this one . . . http://www.databaseanswers.org/data_...jobs/index.htm
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  9. #9
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Quote Originally Posted by Micron View Post
    That would be one PO to many line items on that PO
    If those are parts from your inventory, yes. I couldn't tell if the part was what the customer gave you to service or what.

    I think I get what you're talking about. Instead of ordering parts for a specific ticket, pass them through inventory? Giving me the ability to sell parts I may already have?


    Quote Originally Posted by Micron View Post
    If customer gives you 4 identical items to fix (4 lawn mowers?) I presume that's one ticket. Are you going to hold the entire order until all are completed? Or do you create 4 tickets for 4 items from the same customer, possibly quadrupling your data input just so that you can multi-invoice and not hold up the return of even one item because you can't partial invoice?

    I was. But now that you put it that way...So you're thinking about a many to many between tickets and equipment? And then one to many between ticket and invoice?

  10. #10
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Quote Originally Posted by moke123 View Post
    You should take a look around Barry Williams' site http://www.databaseanswers.org/data_models/index.htm
    He has hundreds of data models like this one . . . http://www.databaseanswers.org/data_...jobs/index.htm
    Thank you for that. I'll check it out.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So you're thinking about a many to many between tickets and equipment?
    I don't know enough about the business to answer that. With each post a new tidbit of info about the process is revealed, but that's not really the way to do this. If you want to pursue design I think a synopsis of the business process would be a big help. You'd start at the high level (I run a business repairing tools that customers own) and drill down (I have parts but I sometimes have to order parts for the job that I do/don't mark up in price. Each tool is/is not a separate work order thus I will/might never, partially invoice. I do/don't need to account for labor. There are employees at different pay rates/there are no employees, etc. etc.)

    And then one to many between ticket and invoice?
    Only if you think that you should provide for associating 2 or more invoices with the same job/ticket. All it should take is changing the relationship from 1:1 to 1 ticket to many invoices.
    Last edited by Micron; 02-15-2021 at 04:30 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.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    lefty2cox,

    I agree with Micron with respect to the need for more info on the business and process(es) that your proposed database is intended to support.
    If you could give us a plain English description of
    -a day in the life of your business, or
    -follow a Customer request from start to finish
    it would be very helpful for you and for readers.

    We really need to know WHAT you are trying to do and the things involved. And once that is clear, you will get focused responses on HOW it may be accomplished with MS Access.
    Last edited by orange; 02-15-2021 at 05:46 PM. Reason: spelling

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I like the way that was described. If I may add to
    -follow a Customer request from start to finish
    it's probably a good idea to describe such an event as the worst case/most complicated scenario.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Right now, a neighbor, friend or friend of a friend calls me to say...."my snow blower is broken". They bring it down or I go get it. I fix it and give it back to them (either they pick it up or I deliver it). Most of the time, I have to order parts to fix it. Although I mostly charge cost for the parts, I do want the ability to mark up. There is labor involved. Rinse and repeat. I do keep a small number of parts on hand. Oil, spark plugs, belts and such. So there is a small amount of inventory right now. I know that is a very simplistic view of a business that can involve more complexity. But honestly, that's all it is right now and I'm new at both. I guess I'm trying to straddle two worlds. Since I have some basic understanding of Access I thought I'd build something to track this simple hobby that could eventually be a business. But then I start thinking about the possibilities and my OCD takes over. Right now, I have a completely unrelated profession. But I enjoy fixing snow blowers and building databases (with my limited knowledge), way more than I enjoy my day job. Point being, this operation I'm building for is more of a hobby right now. But I'm trying to build a database that could absorb some volume and something a little more serious... just in case.

    I have no problem with under building or overbuilding. If I under build, I have the pleasure of learning and building again. If I overbuild, I had that fun as well.

    After reading what I just wrote, I may need a psychiatrist. I hope that helps in some way.

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good morning.
    No psychiatrist yet.

    From your description:
    -Client calls with SnowBlower issue
    -Receive problem item (They bring it down or I go get it).
    -Symptoms/issue description
    **see links below re snow blower repair/service
    -order parts for repair
    -charge cost for the parts with ability to mark up.
    -There is labor involved.(possible fee schedule/estimate for completion..)
    -Repair item
    - Return item to Client (either they pick it up or I deliver it)
    - There is a small Inventory of parts/consumables( Oil, spark plugs, belts and such) to be maintained

    No formal contract, work order, invoice (paper work generally)????

    I have a couple of small outboards, snow blower, lawn mower and generator. And a passing interest in minimal DIY repair/maintenance of same.
    You may find these links of interest.
    -see https://weingartz.com/expert-advice/...and-solutions/
    -see youtube donyboy snowblower
    -see youtube mustie1 snowblower
    -see youtube steve's small engine saloon
    Last edited by orange; 02-16-2021 at 07:45 AM. Reason: spelling

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 08-05-2016, 11:53 AM
  2. Replies: 2
    Last Post: 07-27-2016, 10:25 AM
  3. Help with Relationships
    By indians207 in forum Database Design
    Replies: 7
    Last Post: 11-25-2013, 09:53 AM
  4. Explicit Relationships and Implicit Relationships
    By Dazza666 in forum Database Design
    Replies: 2
    Last Post: 07-17-2013, 02:11 AM
  5. Relationships
    By IT_GIRL in forum Access
    Replies: 3
    Last Post: 06-27-2011, 02:27 PM

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