Is this table join type the right one? (1 Viewer)

[sjs94704]

Here is a picture of my relationships of my database. At the top left you will see that there is an "Entity" (The Customer). They make a transaction. One type of transaction is just a single work order to do a small job, such as going to someone's house to fix their sink.

The second type of transaction is where the customer hires us to do a job for them, such as replace the sinks in an entire apartment complex in which case a job is created and from that job, multiple work orders are created.

So, if I call your attention to the relationship between the TransactionT table and the JobT table.

Here is the criteria for the relationship:

Many transactions can have jobs
There can only be ONE job for each transaction

I am just checking myself. Is that how this is set up now or do I need to change anything?

 

[The_Doc_Man]

Based on your description, this is not quite right.

At the top left you will see that there is an "Entity" (The Customer). They make a transaction. One type of transaction is just a single work order to do a small job, such as going to someone's house to fix their sink.

The second type of transaction is where the customer hires us to do a job for them, such as replace the sinks in an entire apartment complex in which case a job is created and from that job, multiple work orders are created.

You have claimed that jobs come from entities - which statement in English makes perfect sense. But there is no direct link visible between entity and job. Surely, you expect repeat business such that you could have multiple jobs depending from a single entity record. I might take that relation from EntityT:EntityID that leads to TransactionT:EntityID and instead let it terminate at JobT:EntityID. Then potentially drop the field EntityID from the TransactionT table altogether, since you can get that by back-tracking to JobT:EntityID through a JOIN query at worst.

Your picture cut off at least two tables so no advice there.

 

[sjs94704]

Based on your description, this is not quite right.



You have claimed that jobs come from entities - which statement in English makes perfect sense. But there is no direct link visible between entity and job. Surely, you expect repeat business such that you could have multiple jobs depending from a single entity record. I might take that relation from EntityT:EntityID that leads to TransactionT:EntityID and instead let it terminate at JobT:EntityID. Then potentially drop the field EntityID from the TransactionT table altogether, since you can get that by back-tracking to JobT:EntityID through a JOIN query at worst.

Your picture cut off at least two tables so no advice there.

First of all, YES! You do want a person to come back for repeat business.

I actually started out the way you described with the criteria of;

A customer can have many jobs and,
Each job can have many work orders

So, there was the

Entity table
Job table and
Work Order table

I guess the dilemma that I am trying to work with here is if in one customer's case I know I will be creating a job that will be requiring multiple work orders to complete, go one way, BUT, on the other hand, if I have a very small job that I know for SURE is only going to require a single work order, how can I handle the data entry of that without putting the user through the experience of having to fill out ALL the screens for an entire big job for just one work order? And how can I set my tables up to handle these two types of situations?

So, I was simply attempting to see if I could create this TRANSACTION table to help me cope with that part of the challenge. This was my first attempt.

UPDATED RELATIONSHIPS DIAGRAM BELOW:
Per the suggestion by the_doc_man I made the following changes;

I removed the JobID from the TransactionT table which also deletes the relationship between the TransactionT table and the JobT table.
Created relationship between EntityT table and JobT table

This is the resulting updated relationships:

So, if I am understanding right, by this relationship I should now be able to either create a single work order by using the transactions table, (i.e. - A button on a form that might say "Create Work Order") and it would take me directly to the Work Order screen and I can proceed from there and if I want to create a Job then I would have another button that would say "Create Job" which would take me to the screens that are for creating a job and so on...?

 

[The_Doc_Man]

Well, the order of use of those buttons would depend on the relationships. But you would have to follow the relationships one at a time because of the "no orphans" rule that applies when you declare relationships. So...

First you select/create a customer in the entity table.
After discussion/negotiation, you create a job in the job table.
Based on the discussion, you create work orders as needed.

But now I see one more technical "gotcha" that needs to be addressed. You have two explicit relational paths to get to "Transaction" and that is not going to work. You can get there directly from EntityT and indirectly from EntityT to JobT to WorkOrderT to TransactionT. And that is not allowed. What happens is that when you do something to create queries, forms, or reports with the wizard and you have two defined paths, that is ambiguous and Access might do something inappropriate to the situation.

I'm not sure what you mean by "Transaction" in this model but I would remove the direct Entity dependence and just recognize that you might have to back-track or do a DLookup or something similar for finding the Entity associated with a Transaction. As long as you don't have the relationship between the two, you COULD keep the Entity in the Transaction table - but doing so would be a normalization violation as it would appear that your transactions depend on a work order. I.e. no work order, no transactions - even if you have a customer entity.

 

[sjs94704]

Well, the order of use of those buttons would depend on the relationships. But you would have to follow the relationships one at a time because of the "no orphans" rule that applies when you declare relationships. So...

First you select/create a customer in the entity table.
After discussion/negotiation, you create a job in the job table.
Based on the discussion, you create work orders as needed.

But now I see one more technical "gotcha" that needs to be addressed. You have two explicit relational paths to get to "Transaction" and that is not going to work. You can get there directly from EntityT and indirectly from EntityT to JobT to WorkOrderT to TransactionT. And that is not allowed. What happens is that when you do something to create queries, forms, or reports with the wizard and you have two defined paths, that is ambiguous and Access might do something inappropriate to the situation.

I'm not sure what you mean by "Transaction" in this model but I would remove the direct Entity dependence and just recognize that you might have to back-track or do a DLookup or something similar for finding the Entity associated with a Transaction. As long as you don't have the relationship between the two, you COULD keep the Entity in the Transaction table - but doing so would be a normalization violation as it would appear that your transactions depend on a work order. I.e. no work order, no transactions - even if you have a customer entity.

I guess the other way would be to eliminate the transaction table altogether.

I could just include ALL the fields in the JobT table that I would need in either situation and then using just buttons, queries and screens to present the user with what they need, either just a single work order on the screen (even though a job is being created in the tables) or, create an entire job altogether as required by the circumstances...

sound more reasonable and doable?

Like this: ...

 

[The_Doc_Man]

sound more reasonable and doable?

That is a question I cannot ever answer. Here is why.

You are the subject matter expert. Access can build all those tables, queries, forms, reports, macros and modules and it can define relationships. I can tell you the wisdom of certain setups. But in this discussion, only YOU know which layout best represents the reality of your business. I can answer that "doable" for Access is USUALLY a "yes" - but "reasonable" depends on the reality of your business flow. Never get rid of something that is a "real" thing to do if you were doing this on paper. But I don't know what you put on paper.

Your layout was actually pretty decent except for the couple of suggestions I made. It appears that you had gone through to find out what logical entities (including customer Entities) you needed to represent. I could have imagined a business flow based on that design. So trust yourself to know what the BUSINESS needs to have represented. And if you had a real use for transactions, keep them. If they were not a real thing, drop them. But ask yourself this question: Why did you include them in the first place? Do you ever have multiple "things" to be recorded as transactions? Because if you merge them into work-orders, you can only have one (now implied) transaction per work order. Does THAT make sense in your context? This is the way to decide what needs to be in your tables. What is the reality of your business?

 

[jdraw]

sjs94704,

Have you created/mocked-up some sample data and sample transactions (what-ifs/ basic business rules) and tried to work those against your model? Less effort required to get started than working with a physical database. See this for more info.

 

[sjs94704]

That is a question I cannot ever answer. Here is why.

You are the subject matter expert. Access can build all those tables, queries, forms, reports, macros and modules and it can define relationships. I can tell you the wisdom of certain setups. But in this discussion, only YOU know which layout best represents the reality of your business. I can answer that "doable" for Access is USUALLY a "yes" - but "reasonable" depends on the reality of your business flow. Never get rid of something that is a "real" thing to do if you were doing this on paper. But I don't know what you put on paper.

Your layout was actually pretty decent except for the couple of suggestions I made. It appears that you had gone through to find out what logical entities (including customer Entities) you needed to represent. I could have imagined a business flow based on that design. So trust yourself to know what the BUSINESS needs to have represented. And if you had a real use for transactions, keep them. If they were not a real thing, drop them. But ask yourself this question: Why did you include them in the first place? Do you ever have multiple "things" to be recorded as transactions? Because if you merge them into work-orders, you can only have one (now implied) transaction per work order. Does THAT make sense in your context? This is the way to decide what needs to be in your tables. What is the reality of your business?

OK, I read what you said here and thought it through some more and took what you said about the TransactionT table and decided to put it back because there ARE uses for it, such as SALES, PAYMENTS, REFUNDS, and ADJUSTMENTS.

If you notice by the relationships diagram I have added a SalesOrderT table along with it's supporting detail table. Keep in mind that I have not got every single field I will need yet in each table, but this will provide something to work with. I might add more later if needed.

As I have been working with this I realized that I have probably been just over thinking this part having to do with work orders, so I just decided to make it simple.

So, the flow will go like this:

1. If a customer calls to have us come to repair their sink, OR if the apartment complex calls to replace all the sinks in every apartment, then they are BOTH still JOBS! This keeps the table relationships cleaner and the workflow smoother. I will have a button for 'Simple Job' and 'Regular Job'. A simple job is one that would allow the user to input just the minimum needed for an 'Individual person' Entity to get that one work order filled out that they need and a regular job is one that requires complete job details because it is a bigger job.
2. If someone just buys an item or a part, etc., then a Sales Order is generated.

For my next trick I need to start building tables for money!

I am a business oriented person, but, when it comes to keeping track of money I could use some suggestions here as far as table structure. I have a feeling it might not be so simple!

As far as a business rule, I am wondering if it would be the smartest thing to have customers be able to only pay the job in full or make payments on a job, or, if they have multiple work orders on a single job, should I let them be able to pay on a specific work order or pay off a specific work order as well as make payments toward the entire job?


Here is my modified table structure before thinking about money stuff:

 

[The_Doc_Man]

As far as a business rule, I am wondering if it would be the smartest thing to have customers be able to only pay the job in full or make payments on a job, or, if they have multiple work orders on a single job, should I let them be able to pay on a specific work order or pay off a specific work order as well as make payments toward the entire job?

What was the business practice before you started automating everything? There is an old guideline that you should never let the tail wag the dog. I.e. don't make the business conform to the app. Make the app conform to the business.

 

[sjs94704]

What was the business practice before you started automating everything? There is an old guideline that you should never let the tail wag the dog. I.e. don't make the business conform to the app. Make the app conform to the business.

Maybe I was not clear from the beginning. There is not an existing business, so there is no existing business rule. I am creating a DB with what I am going to call 'core features' that I can build off of when a new client wants to buy the DB, I want to have 'something' to promote to potential clients.

So, that is the reason I am asking about the type of business rules the way I am. I get it when you say that answering this would be impossible to answer until it is known what the business rule of the actual client is, but, so for simplicity sake and to be able to demonstrate it's abilities, I just something that works so that potential clients can see a working model of my DB/ They can then request any changes to fit their business rules as required.

 

[The_Doc_Man]

OK, this is therefore a speculative project. Be prepared to be flexible. Once, a LONG time ago, I worked for a company that made petroleum pipeline control systems. We got the baseline system running and decided that the first improvement would be leak detection. We did that, but allowed customers to add their specifications. After a few years, we had 25 different leak detection methods... ours and those of 24 different customers.

With your explanation, NOW is the time to include VERY DETAILED internals documentation because as time passes and you (hopefully) get bunches of customers, you will need to remember how it REALLY works. Trust me, after a few times modifying the code, you will face that moment when you can't recall what the module is REALLY meant to do.

 

[sjs94704]

OK, this is therefore a speculative project. Be prepared to be flexible. Once, a LONG time ago, I worked for a company that made petroleum pipeline control systems. We got the baseline system running and decided that the first improvement would be leak detection. We did that, but allowed customers to add their specifications. After a few years, we had 25 different leak detection methods... ours and those of 24 different customers.

With your explanation, NOW is the time to include VERY DETAILED internals documentation because as time passes and you (hopefully) get bunches of customers, you will need to remember how it REALLY works. Trust me, after a few times modifying the code, you will face that moment when you can't recall what the module is REALLY meant to do.

Yes, I will go along with your term of speculative project. I call it 'Work Order PRO'. A long time ago I build a very basic version of the same thing, but, I want to totally start from scratch and think outside of the box more. It has been over a decade since I did that. What I do remember is that payment was only allowed to be made on the JOBS.

So, getting back into my thoughts about charges, payments, credits, adjustments and all of that.

Regardless of what Entity they are posted to/from and what method, meaning a sales order, invoice or a job, is coming up with a schema of what tables would be needed to handle all of this.

As far as work orders are concerned, once they are marked as COMPLETED then it cannot be changed. If a problem happened and needs to be corrected, a NEW work order must be created and can be marked as a RELATED TO the first one so there is a connection made that way and appropriate documentation can be entered about what they are doing. I will make space (tables if needed) to account for this event.

I mention all of this because it has a direct effect on how charges work. It would get incredibly messy if there was a work order for $10 that got charged to the account, marked completed, then someone comes and gives a $3.00 credit on that specific work order. That $3.00 credit should be posted to the customer's account with a detailed explination of the reason, but not to be altering the charges that have already posted to the customers account. Doing this way is much cleaner and more organized and allows all the customer service people the ability to more easily follow the 'trail' of what has happened on the customer's account.

 

[Pat Hartman]

with your latest model, you have reproduced an earlier problem with new tables. Every entity needs at least one contact. You now have two relationships with contact information and you have created a potential nightmare situation because it allows multiple contacts to link to the same phone record. So, if Sally changes her phone number, do you really want it to affect suzie? Get rid of the EntityT - EntityPhoneT relationship and just go from Entity to contact to contactDetails (replacement for phone). This also eliminates EntityContactsPhoneT.

Also calling the contact information "phone" is pretty limiting. it should allow for ANY type of contact. Do not exclude email, FB, etc. This table should support all of these.

The WorkOrderDetailHistoryT doesn't seem to have a purpose.

And finally, ALL tables need to have defined relationships.

One situation I have run into when doing a similar project is that a company may have many contacts but a Job can only relate to a few of them. So I do create a junction table that is Jobs to Contacts which on the surface creates the type of situation we have just told you to avoid. You need to be careful to filter the combo in the form that manages the junction table to limit contacts to the specific entity so you can't pick contacts from Acme when you are working on a job for Ace. This then allows me to fill the combo box for the contacts for a job so it is limited to the correct people.

 

[sjs94704]

with your latest model, you have reproduced an earlier problem with new tables. Every entity needs at least one contact. You now have two relationships with contact information and you have created a potential nightmare situation because it allows multiple contacts to link to the same phone record. So, if Sally changes her phone number, do you really want it to affect suzie? Get rid of the EntityT - EntityPhoneT relationship and just go from Entity to contact to contactDetails (replacement for phone). This also eliminates EntityContactsPhoneT.

Also calling the contact information "phone" is pretty limiting. it should allow for ANY type of contact. Do not exclude email, FB, etc. This table should support all of these.

The WorkOrderDetailHistoryT doesn't seem to have a purpose.

And finally, ALL tables need to have defined relationships.

One situation I have run into when doing a similar project is that a company may have many contacts but a Job can only relate to a few of them. So I do create a junction table that is Jobs to Contacts which on the surface creates the type of situation we have just told you to avoid. You need to be careful to filter the combo in the form that manages the junction table to limit contacts to the specific entity so you can't pick contacts from Acme when you are working on a job for Ace. This then allows me to fill the combo box for the contacts for a job so it is limited to the correct people.

Well, what I am trying to handle is that an 'Entity' (in the case it is a company) might have multiple employees who are simply 'Authorized Buyers'. This means that let's say that the Entity is Steven's Plumbing and I have my technicians John and Jack who works for me. John and Jack both has the authority to call and place work orders and buy parts and merchandise under my company name. But, neither is actually a customer themselves. Does this mean that they are still considered an entity? That was the motivation for that extra ContactT table. It was not intended to be a requirement to have one. Only if there are other people involved as I have explained.

As far as the PhoneT tables, I get what you mean, so I am just going to change it to MediaT (includes phone and all social media, internet, websites, etc.) … and do what you suggested.

 

[Pat Hartman]

An Entity, is an individual or a company that your application interacts with. An individual might be a customer. A company might be a customer. In some applications, an "individual" Entity might be an employee, a teacher, a student, or a parent. But, companies (and sometimes individuals) might have different roles. They might be a vendor for example. A Customer and a Vendor have many overlapping attributes and a couple that might be unique to one type or the other. For example, a vendor might be a plumbing company that services your office or it might be a plumbing supply company from which you buy parts. If the application is being used by a College, the Entity might be a parent, a student, and a teacher.

It is very hard to define an "air" spec which is what it seems like you are doing. You need to step back and think about your target audience and perhaps narrow your scope a little. I'm also not certain you understand the whole Entity concept yet or why someone suggested it. The point of the Entity concept is to reduce to one, certain types of tables such as address, contacts, notes, contactDetails. What this does is to allow you to create one form to manage addresses. One form to manage contacts, etc. rather than having to duplicate the forms/reports/queries/code for every type of entity.

 

[The_Doc_Man]

The complication of (a) allowing a company to be a customer entity and (b) still wanting personal contacts now leads you to a Person table that is a child of the Entity table - and in the case of a single person-company or a direct personal customer - you might end up having to have a double entry to manage name-handling on a consistent basis. You might almost do better (to keep your mind-set correct) if you simply change "Entity" to "Customer" because it would appear it is morphing into that status. Just an observation, not a super-strong suggestion. BUT there is that old rule that says "for programming design, a rose by any other name DOESN'T ALWAYS smell so sweet." (OK, it's an old rule for me, but not for everyone...)

 

[sjs94704]

An Entity, is an individual or a company that your application interacts with. An individual might be a customer. A company might be a customer. In some applications, an "individual" Entity might be an employee, a teacher, a student, or a parent. But, companies (and sometimes individuals) might have different roles. They might be a vendor for example. A Customer and a Vendor have many overlapping attributes and a couple that might be unique to one type or the other. For example, a vendor might be a plumbing company that services your office or it might be a plumbing supply company from which you buy parts. If the application is being used by a College, the Entity might be a parent, a student, and a teacher.

It is very hard to define an "air" spec which is what it seems like you are doing. You need to step back and think about your target audience and perhaps narrow your scope a little. I'm also not certain you understand the whole Entity concept yet or why someone suggested it. The point of the Entity concept is to reduce to one, certain types of tables such as address, contacts, notes, contactDetails. What this does is to allow you to create one form to manage addresses. One form to manage contacts, etc. rather than having to duplicate the forms/reports/queries/code for every type of entity.

Your right! In my other application I did split the different tables dependind on the typecof role they played., etc .... But I do see and appreciate that having them all togeher is best. I guess my only question now would be what fields I would need to make the connections between Entities in the way I have explained?

To start with my thought would be:

EntityID
EntityTypeID (Customer, Vendor, Contractor, Service Provider, Employee, etc ....)
EntityRoleID (Owner, Employee, Technician, Repair Man, etc.)
RelatedToEntityID

NOTE: The list of choices in both EntityType and EntityRole will be dictated by the clients needs. I just put something there for now..
To me, it seems like it is this last field that would make the connection (if there is one) with another Entity ..

I hope I am getting close ...

I do want to put a HUGE GIANT "Thank You" out to both you Pat and The Doc Man for all your help on this!

From the little bit that we have talked about in this thread, at least for me, I have learned so much from where my development skills were before! This issue might be second nature for you now, but for someone who has never done it this way before because they were taught another way it is a huge leap of improved skill on my part, so thanks!

 

[Pat Hartman]

Doc and I have no idea what this application will ever be used for. I think you don't know either. that makes certain things hard to pin down. For example, you are thinking of of type and role differently than I would think of them. I would use type to separate individuals from institutions. Roles would be how the entity is used in the application - and this is not at all defined because you don't know what type of application the Entity would be used in. So, I'm going to suggest you pick one target - an order entry application, a school management application, an inventory application, etc and flesh out the ideas with that type of application in mind.

Roles are how the entity is used in the application. Are they customers? ie can they buy things if this functionality is used in an order entry application. Customers are used in tables relating to orders. Are they vendors? vendors are used in many types of applications but they tend to be entities from which we BUY things. That means that they are probably associated with Purchase orders and tables related to PO's There are multiple classes of vendors. Vendors can sell services or products or both. The products can be for internal use like pens and file folders and toilet paper or they can be nuts and bolts or other hard goods used in your manufacturing process. The business will always want to separate these two types of purchased goods for reporting reasons.

You also seem to be confusing Entities with contacts and merging their usage. For example, if you are hiring a company to do work for you, the individuals are not entities, they are "contacts" which is a general class of people who work for a company you do business with. If you need to know Joe is a plumber, then you can add something like that to the "contacts" section. Probably a child table because people can have multiple skills. A contact could be the owner, a sales person, or a worker that will be sent to your site to perform a task.

You are unable to focus because you don't have a clear usage. You seem to think that you can design a piece of software that is all things to all people. Well, just talk to anyone who has to work with an ERP or a smart phone. They claim to do everything, the problem is that they do nothing well.

Start by clarifying your target application. Flesh it out. Don't get sidetracked. Then once the schema satisfies that usage, we can think about how it can be twiddled to expand its usage to other types of applications.

 

[The_Doc_Man]

My late father-in-law was a carpentry contractor. (Built houses.) He lived by the old rule "measure twice, cut once." There is a less eloquent version of that for programming: Agonize miserably over the design lasting several nights, implement once. Doesn't roll as trippingly off the tongue - but it is often true.

I take an oddball spin on lots of things but this much is true. The more time you spend up front thinking about what you plan to do and the implications of those planned actions, the better off you will be when you have to actually DO something with what you built. There is NO substitute for up-front design activity.

 

[sjs94704]

Doc and I have no idea what this application will ever be used for. I think you don't know either. that makes certain things hard to pin down. For example, you are thinking of of type and role differently than I would think of them. I would use type to separate individuals from institutions. Roles would be how the entity is used in the application - and this is not at all defined because you don't know what type of application the Entity would be used in. So, I'm going to suggest you pick one target - an order entry application, a school management application, an inventory application, etc and flesh out the ideas with that type of application in mind.

Roles are how the entity is used in the application. Are they customers? ie can they buy things if this functionality is used in an order entry application. Customers are used in tables relating to orders. Are they vendors? vendors are used in many types of applications but they tend to be entities from which we BUY things. That means that they are probably associated with Purchase orders and tables related to PO's There are multiple classes of vendors. Vendors can sell services or products or both. The products can be for internal use like pens and file folders and toilet paper or they can be nuts and bolts or other hard goods used in your manufacturing process. The business will always want to separate these two types of purchased goods for reporting reasons.

You also seem to be confusing Entities with contacts and merging their usage. For example, if you are hiring a company to do work for you, the individuals are not entities, they are "contacts" which is a general class of people who work for a company you do business with. If you need to know Joe is a plumber, then you can add something like that to the "contacts" section. Probably a child table because people can have multiple skills. A contact could be the owner, a sales person, or a worker that will be sent to your site to perform a task.

You are unable to focus because you don't have a clear usage. You seem to think that you can design a piece of software that is all things to all people. Well, just talk to anyone who has to work with an ERP or a smart phone. They claim to do everything, the problem is that they do nothing well.

Start by clarifying your target application. Flesh it out. Don't get sidetracked. Then once the schema satisfies that usage, we can think about how it can be twiddled to expand its usage to other types of applications.

I believe I do understand the concept of Entity. I might have some of the terminology used differently than you at the moment, BUT, I am not married to my ideas so I am very open to hearing what you are talking about and how you are looking at this such as how you see the use of type and role as well as anything else you find useful.

In my mind it makes total sense about the Entity concept. I agree that it makes it so much easier to have only ONE form for all the data entry and so forth which makes coding so much easier!

I guess my ONLY sticking point is when it comes to relating Entities with each other, so when I have an Entity (a customer say in the case of being a company) that has several people working for them who could be calling in to place a work order under the company name. I have no problem with entering those employees of that company as entities as well, but I just need to connect them with the company that they work for.

That way when one of them calls in to place a work order I can pull up the company name record and each person who is linked to that company will show up in a list and I can select the person who is placing the order.

In my other database I had a:

CustomerT table and an AuthorizedBuyersT table. The Authorized buyers were those other people who were connected with the customer who could call in to handle work orders. But, it is a separate table.