Seeking advice to attempt Vehicle Database Tables (template based) Access2007 (1 Viewer)

[DannySeager]

For the reasons I posted above I don't believe having seperate tables is less work.

It's the sort of thing that will be fine for a while and then you'll need to add a field and then you have to add it to many tables and many forms.

As I also stated once you have a supplier who also buys a car from you then your users will start asking why they have to enter all the data twice... and then when they change thier address the data only gets updated in 1 of the records... so you have incorrect data due to poor (perhaps too harsh a word) table design.

Store data once wherever possible.

 

[speakers_86]

You didn't see me edit, did you?

 

[DannySeager]

Sorry I missed your edit or you were typing it when I was typing my message.

As I say you can do it the other way.. but I wouldn't recommend it. I am welcome to hear any other opinions on the subject but I honestly can not think of a good reason not to store records of the same structure in the same table.

While I'm posting I would also say that you should break the contacts out into a "company contacts" table and I generally break contact information (email, telephone, fax, mobile) into a seperate table with a structure like

[B]TblContactNumbers
[/B]ContactNumbersID (PK)
CompanyID (FK)
ContactID (FK [COLOR=Red]#[/COLOR])
ContactNumberTypeID (FK)
ContactNumber (stored as text)

[B]TblContactNumberTypes[/B]
ContactNumberTypeID (PK)
ContactNumberType

Doing it this way allows you to have

• As many or as little contacts against a company
• As many or as little communication methods against a user or a company
• A flexible system that will allow for the addition of communication types (ISDN, Pager, IM etc) without forcing a database redesign.

# = To the newly created contact table. Note that this field will be 0 if the contact number is applicable to the company as a whole and not a specific contact.

 

[gemma-the-husky]

Danny

i can see the idea, and I know you are an MVP.

but its one thing solving technical problems, and another thing using systems in the real world. To me, they are different things. A supplier and a customer are two different real world entities, and although they may overlap and you could store these details in a central table, I really do think in some ways it makes it harder.

eg do you have a flag against each contact to identify whether that contact is a customer/supplier - so when you enter a sales order, how do you ensure you select from customers only, and exclude the suppliers - and how do you deal with a situation where a customer becomes a supplier. The clerk maintaining the contact file needs to know the contact is already in there as a customer, so he can just set the account as a supplier account also. The guys dealing with accounts payable/accounts receivable dont know each others accounts - We are probably talking big systems, and my guess is you still end up with a load of duplication. Accounts set up multiple times, once as a Supplier and again as a Customer, with a different account id.

Ok, by having 2 tables, you may lose the ability to easily summarise sales AND purchases against each entitiy - but you gain a lot of simplicity - and in most modest systems there will be far less crossover than you think.

for a similar reason you wouldnt store a purchase order and a sales order in the same table, even though they are both orders, and probably both have many similar fields. So you could. But they are different realworld entities.

practically, which sort of commercial systems have a central contacts table for suppliers and customers? most accounting systems i know have separate tables for these things.

[similar thing - would/do medical systems have a "persons table" storing details of both doctors and patients. my guess is that most would keep the two separate. yes, a doctor may become a patient - but if he does its a different thing]

 

[Proverbs]

A bit scared to post this. Gentlemen. Wow(!) and thank you for giving of your time. Could I put a meek hand up and, if it’s not been too impolite, ask for some simplicity based on a) my level of knowledge and b) the following word picture of this task and why (‘me’ you can see from my profile which is relevant to the below) I hope I’m not being too ungrateful in hopefully going Back to Basics.
I now work part time, supplements the pension, answering phones and a bit of typing for a family business of husband, wife, two sons and a daughter (that’s the Partnership) but they also have a Ltd Company (one Co. does one bit, one Co. does another) Been going +/- 40 years building steel portal framed agricultural barns. They have 10 employees (welders & erectors) plus me (again, see my profile and I hope I’m not unintelligent). They have 10 vehicles (vans; flat bed HGV, and buying an artic with two trailers. There are 7 pieces of plant (digger; side loader type) plus their private vehicles. No big fleets, nothing complex - may (replace) buy or sell a vehicle/plant once a year.
With vehicles and plant, would like to record who sold it to us and who bought it from us (when subsequently sold on). Would like to know how it was acquired and if with borrowed money, from whom and the terms. Which business has it as an asset (these vehicles with X; these with Y). Record the expenses. Fairly simple things such as this, but not inclusive of, on a small scale.
Now this could all be done (is already in part) in Excel, of which I have a heap load more knowledge than Access but it’s not the way. A well built database is the way forward and for me to learn for a more important task as follows.
They have NO stock control. (Great people, great handmade product, that’s what they are good at but little if no quality technological administration or ability). Part of improvements would be to create a stock control database (for bolts and bandsaws through to steel and roofing sheets and so on). But I needed a starting point to understand (learn?) Access with a simpler task – hence vehicles - and build on what I learn because stock will have a whole lot more issues.
I believe I have a good start with this and all I’m struggling with is the Finance Company side and how they relate when a particular agreement can only relate to one vehicle (One to One?) and to overcome it but the conversations being had don't seem to address this problem simply in ways I can understand and apply. Please believe me I am so very grateful to you all and I can just about see both sides of the debate about inclusive (Danny) or separate (Dave) Tables for Suppliers, end Purchases (when sold on at use end) and Finance Co’s.
If I've gone on too long or broken any forum etiquette in ignorance then I apologise in advance.
I have a trip on my own to make tomorrow evening through to Friday so I may not be able to give the proper attention any further help deserves until Saturday, though after this post the Moderator (or yourselves) may well want to call time on me, which I can understand.

 

[DannySeager]

I am an MVP but that doesn't mean that I am not capable of being wrong... but in this case I still hold that 1 table is the most flexible way of doing it (and generally the most flexible way is the right way).

do you have a flag against each contact to identify whether that contact is a customer/supplier

No - you have a seperate junction table which will allow you to add seperate company types without having to redesign your tables.

We are probably talking big systems

I'm not sure that the size of the database should change the layout of the tables... if a database is correctly normalised then it should be capable of growing from a small database to a large one without a major rewrite.

for a similar reason you wouldnt store a purchase order and a sales order in the same table, even though they are both orders, and probably both have many similar fields. So you could. But they are different realworld entities.

I have seen (and worked on) databases that have not only orders and Purchase orders in the same table but the table also had invoices and deliveries in it as well... it worked fine - but I have to confess it's not my personal preference.

practically, which sort of commercial systems have a central contacts table for suppliers and customers? most accounting systems i know have separate tables for these things.

I have seen systems that do have them all in 1 table and systems that don't - but out of the systems I have seem (Mainly MIS/ERP systems) most of them do.

[similar thing - would/do medical systems have a "persons table" storing details of both doctors and patients. my guess is that most would keep the two separate. yes, a doctor may become a patient - but if he does its a different thing]

Why not... a person is a person... they have titles and first and last names and DOB's etc.... There would be very little information that would differ from a doctor and a patient that is stored in the "Person" table.

Please do not think that I am arguing with you or being awkward... just trying to make my point.

 

[gemma-the-husky]

Danny

thanks for the views.

i am still struggling a little to understand exactly how the junction tables are put together to collect the suppliers/customers. I can still see names getting duplicated because the user doesnt realise the account is already there - but I guess when you see it and use it, and especially are used to developing in this way, it works fine, and its obviously a model you are used to. And I dont suppose the user needs to know how anything is actually implemented anyway, if it works.

 

[DannySeager]

As far as I am concerned a user specifies how they want the system to work.. and they have no need to know how you achieve that.

Most users don't have a clue about table layouts and normalisation... they don't need to know... just like I don't know how to do thier jobs.

As for the junction tables.. these are how I proposed it should be done

This is the lookup table where you add new company types (maybe "prospect" or "Partner" or "factory" - the key is that the users can add any company type they want as it's designed to flexable handle them

TblCompanyType
CompanyTypeID
CompanyType

and then you have the junction Table that links the company record with the company type record(s)

TblCompanyTypeJunction
CompanyTypeID
CompanyID

So if the company was just a customer they would have 1 record in the junction table (with the companyID and the CompanyTypeID for customer)... but if they were a supplier, a purchaser and a Finance company (I know unlikely) then they would have 3 records in the junction table.

As for stopping the users entering the companies more than once... I would just do a check on the company name and if anything matches it partly then show they users and make them acknowledge that this is a new company and does not exist. The problem never goes away though... if you seperate the tables for all the company types you have to perform that check when they want to create a new company but you have to code it (and hard code it) to check the specific table (i.e. the suppliers table or the purchasers table) because if my experience is anything to go on users don't check to see if they are doing something right... they just tap away... - maybe I have bad users.

It sounds like the database that Proverbs is working on will be a very small system so they will most likely have no issues either way they work.. but if they are using this as a learning block to making a stock control database (and they can be complicated) then it's important to know/understand how you would set your tables out up to the 3rd Normal Form... even if you then make a concious decision not to - and there are many factors that will help make that decision... there are for example databases that are far more efficient having lots of columns to search through rather than normalising the data but the decision to break from 3NF was a concious one so when you do that you expect and hopefully know what and how you need to work around the move from normalisation

I would also add that I am very pleased to be in this thread with Dave... I've lurked around this forum for a long time without having time to put much into it but I've noticed that there are a few users who really put in the extra effort to help and I class you among them.