Problems with "SpareParts" Database. (1 Viewer)

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:06
Joined
Apr 1, 2019
Messages
713
Friends, I'm in the process of writing a stock control database & intend to adapt the "Allen Browne" method of calculating stock on hand. It's to do with a primary asset and allocated spareparts to that asset. When I 'scoped' out the project I designed a many:many relationship with contacts (that is suppliers) only to realise later that 'contacts' maybe suppliers and/or customers. So I'm in the process of adapting my relationships to suit. I've had a prolonged brain fade & am now stuck. If you open 'frmPart' and select the 'suppliers' tab as an example, (but repeated similarly in several other hyperlinks), then select the hyperlink, the double click event used to open up the correct form. But since I added the 'link' table 'tblSupplier_tblContact' & changed the relationships, then the hyperlink does not open the correct form. I can see why, as in previous versions the link was between 'tblPurchaseOrders!SupplierID' & 'tblContact!ContactID' now it's to 'tblSupplier_tblContact' field, which is clearly not the same as 'ContactID'. If I've made any sense.

My intention is to have a 'contacts' list in which a contact can be a customer or supplier or both. Not quite there yet..................

If anyone has the time, I'd really appreciate a critical review of my database structure. I often have trouble in the design/normalising/sensibilising(?) process. All help is appreciated. As I say, brain fade has set in!
 

Attachments

  • Equipment V18.accdb
    5.1 MB · Views: 478

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:06
Joined
Feb 28, 2001
Messages
27,001
I'm in the middle of a rather time-consuming project so I can't look at this now. However, if I "boost" it by posting, perhaps someone else might be able to take a look.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:06
Joined
Apr 1, 2019
Messages
713
The_Doc_Man, thanks. Appreciate it. Do you think i've been unreasonable/unclear with my request? You people mentor me so much, without your support there is no way i could have achieved even a megre accomplishment. I'm currently reworking a quoting database that i wrote several years ago on the behest of my boss. He insists that it's better to manually enter data such as product weights rather than calculate it on the fly! We know better but he's the boss. Basically, i'm stripping out much of my code to be replaced with empty fields!. It's people like you & the forum that keep me sane.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:06
Joined
Feb 28, 2001
Messages
27,001
Maybe if I jog your brain a bit...

Suppliers and contacts are both people and have all the same data - as people. The question is, what data do they NOT have in common? That comes back to the idea that a contacts info table could hold the uncommon data for contacts but would point to the common data. Similarly, a supplier table could hold the uncommon data for suppliers but would point to the common data.

The other choice is ALSO viable, though. If you analyze the lists as I suggested and find that only a couple of extra fields are required for suppliers and a couple of different extra fields are required for contacts, then you could just have blank fields where not applicable as long as it isn't too many blanks. BUT... what would kill this, and I haven't looked into the DB to find this out, is whether you ever have a case where the same person is a contact for two different situations, or is a broker representing two or more companies - in which case my first suggestion would be needed.

Now, as to why something worked and then broke... that would take a while to figure out. You've got a few dozen forms (some of which are, of course, sub-forms). You've got a complex DB which would take a while to investigate. What you described, however, is a complex problem in that you had a relationship but then changed one side of it. ("But since I added the 'link' table 'tblSupplier_tblContact' & changed the relationships, then the hyperlink does not open the correct form.") Given the size and complexity of that DB, and the fact that we might not understand your business model, I don't know how much time I could spend looking at it. I'm still not finished with my other project, which is personally import to me, so after taking a quick look, I have one more answer for you.

Do you think i've been unreasonable/unclear with my request?

Hard to say, but I'm verging towards a "maybe" answer on that question. That DB involves a LOT of investigation and there is obviously much work already in it. For us to deconvolute it to answer your question would be a non-trivial undertaking. Sorry if that isn't what you wanted to hear. I think I point you in a direction, though. You "diddled" with a link and table that led you to something else. So go find a white-board, some colored dry-erase markers, and maybe some sticky notes.

Draw out that relationship on the board as it was originally, then next to it draw out the relationship as you have it now. Look for differences. Use the sticky notes to simulate each step of the process of what that link was supposed to do and what it does now. If you can visualize it, you might be able to see where you went wrong.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:06
Joined
Apr 1, 2019
Messages
713
The_Doc_Man, Thanks. I'll have a fresh look & let you know how I progress.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:06
Joined
Feb 19, 2002
Messages
42,981
Depending on your business, it would not be unusual to have Customers who were also Vendors so I would almost always use a single table. For more complex applications, I might use an Entity table and create a separate child table with 1-1 relationship to hold the unique columns for each type of entity. In all cases using this model, EVERY relationship goes to the Entity table. None ever go to the specific type table.

So, the others have suggested using the number of columns that are different for each type as a way to make the decision. That works. You do want some control over which entity is used for which function. And again, there are multiple ways to handle this. If all you care is that you know which entities are vendors, you can have a flag 0 IsVendor and set it to true or false. This allows you to control a combo for example when you are placing a PO so only the vendors show up in the list. If you have more than two choices or you care in all cases, then you probably want to use a separate table to identify what role an entity can participate in. And you would use this many-side table to control your combos depending on what type of an entity you are looking for.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:06
Joined
Apr 1, 2019
Messages
713
Pat, thanks. Yes i understand. You know, if i get this model right, then it pretty much covers all cases. I'll spend some time reviewing my structure. I find this aspect of developing an application to be fascinating & quite a brain 'twist' at times. Thankyou. Will let you know how i progress, maybe a little while as i've gathered a few projects at the moment.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:06
Joined
Sep 12, 2006
Messages
15,614
Personally, I would separate them.

I would rather duplicate a customer contact and a supplier contact, than try to force unnecessary data relationships. For most larger enterprises, it's unlikely to be the same person anyway for any given company, other that at GM or Director level.

If you want to merge them, as others have proposed, and you can get it working, it's not an issue though.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:06
Joined
Apr 1, 2019
Messages
713
Gemma, i thought it was smarter to have one 'contacts' table, but yeh i understand the pros & cons. I thought one less table was a good thing! Cheers.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:06
Joined
Feb 19, 2002
Messages
42,981
Personally, I would separate them.
Good luck keeping the contact info in sync.

It isn't the "one less table" it is the single update point of the same data that is the justification of the single table approach.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:06
Joined
Sep 12, 2006
Messages
15,614
^^^^
I sort of take the view that a supplier contact and a customer contact are different entities. It's up to the users to decide what data is relevant.
They wouldn't necessarily need to see if the same person is in both tables.

It's similar to deciding to have a single "account name/ID" to manage AR and AP accounts where the same entity happens to be both a supplier and a customer.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:06
Joined
Feb 19, 2002
Messages
42,981
It's similar to deciding to have a single "account name/ID" to manage AR and AP accounts where the same entity happens to be both a supplier and a customer
For most businesses, there isn't a lot of overlap between vendors and customers. Also, these apps tend to be developed by separate groups and they would never consider sharing data with another application.

I once did a survey at a large client. They had 25 separate part "master" files. Why, because every time some new application got developed, they reinvented the wheel because no other group would agree to share their table, even on a read-only basis. I discovered this when my project needed a Part "master" and I decided to try to use one that already existed. I was a consultant so wasn't into corporate culture and power silos. What a cluster**** when a year later they came to the point of having to enlarge their part numbers. The ripple effect was huge and because of it, they bit the bullet and consolidated the "master" files into one (mine as it happened because I had already decided to enlarge the Part number field) and assigned maintenance of it to a limited number of administrators. This had the side effect of eliminating all the discrepancies.

At another fortune 500 client, my group's task was to consolidate the customer files that had proliferated around the company. At least there were only 12 of them. But they were created at different times with different technologies so some were VSAM, others were mostly DB2 (IBM's RDBMS) and IMS (a hierarchical database - think of an org chart), and their Australian subsidiary used Access. I actually joined one of their quality teams even though I was a consultant. It was my only viable way of getting the data corrected. I couldn't do it. I didn't have the domain expertise to fix any but the most obvious typos and neither did any of my programmers. The Quality team was charged with cleaning up the data anyway but they were doing it one at a time as they noticed errors or people complained. I was able to provide them with anomalies in bulk so I kept them very busy for a couple of months. The synergy was good and we all got what we wanted - clean data:):) YEAH!!!!

If you have overlap, maintenance is more efficient when the tables are combined. You get NO benefit from having two tables. They only lead to data anomalies. But, if multiple fortune 500 companies (I actually have other examples) can make this mistake, you are forgiven for following in their footsteps.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:06
Joined
Apr 1, 2019
Messages
713
Pat, sounds a lot like a company i know. Very interesting stuff & a great story. A problem we have is with spreadsheets. Anyone can make a basic working spreadsheet, no matter how poorly. People use spreadsheets as databases!. What i've been trying to do is consolidate monolithic spreadsheets into proper normalised access databases. Hence, the origin of this thread. I continue!!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:06
Joined
Feb 19, 2002
Messages
42,981
A noble cause but unless you are actually consolidating the data, you are just propagating the problem using different technology.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:06
Joined
Apr 1, 2019
Messages
713
Yes, Pat. I totally agree.
 

Thales750

Formerly Jsanders
Local time
Today, 06:06
Joined
Dec 20, 2007
Messages
2,061
Depending on your business, it would not be unusual to have Customers who were also Vendors so I would almost always use a single table. For more complex applications, I might use an Entity table and create a separate child table with 1-1 relationship to hold the unique columns for each type of entity. In all cases using this model, EVERY relationship goes to the Entity table. None ever go to the specific type table.

So, the others have suggested using the number of columns that are different for each type as a way to make the decision. That works. You do want some control over which entity is used for which function. And again, there are multiple ways to handle this. If all you care is that you know which entities are vendors, you can have a flag 0 IsVendor and set it to true or false. This allows you to control a combo for example when you are placing a PO so only the vendors show up in the list. If you have more than two choices or you care in all cases, then you probably want to use a separate table to identify what role an entity can participate in. And you would use this many-side table to control your combos depending on what type of an entity you are looking for.
It is very usual, in both construction and in industry to have vendors that are also customers.
Tolling Manufacturing is very common. This is the process where manufactures, in particular chemical manufacturing, provide services for each other on a regular bases.

In Construction, Trades often find jobs and hire each other. Even in Database Development, a developer could find help from someone, and tomorrow be hired by them.

Over all, this is why we base our databases on many to many relationships, Many to Many more accurately describes the real world relationships.
 

Thales750

Formerly Jsanders
Local time
Today, 06:06
Joined
Dec 20, 2007
Messages
2,061
I think two tables, one for Contacts, one for Companies. Then they are distinguished by their many to many agreements with your company (Companies in a multi Tenant Database). The relationship between Companies and Contacts should be many to many.

Companies can be any of the standard relationships, Customer, Vendor, Government entity, Banking, whatever. You define them in Contracts or Agreements.
You will need to make Universal Connections if multiple Tables can be the source for Foreign Keys in the same Field.

We accomplish that by having the Table Numbered. The number provides the second Field in a Multiple Field Key,
 
Last edited:

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:06
Joined
Apr 1, 2019
Messages
713
@Thales750, pretty much what i've designed too.Thanks
 

Users who are viewing this thread

Top Bottom