Beginner - General design

[RaSk]

Hello,

First of all sorry for my English I'm French
I'm new to Access, I've some skills on Excel but never used Access in my life (shame on me!).

I'm doing a project for my company: updating the commercial management system, including invoicing, contracts, payments overdue and all that kind of stuff. The problem is that everyone uses his own Excel spreadsheet and nothing is linked together, it's a real mess on the file sharing server!
My idea was to store all those information in an Access database. Actually it was not my idea, I was trying to do it on Excel and posted my idea on a forum and they strongly recommended me to use Access instead.

I'm only in the general design of the database, nothing is developed at the moment, and I'm already struggling with some design issues. Here's an example with questions :

Table: tblClient
Fields:
ClientID (primary key)
Name
PC (Parent Company)
Adress
BD (Business developer)

1) Should I refer "client" in every field? Like in "ClientID"? Should I use "ClientName" for example? Or is it recommended only for primary key?
I don't know why but I have the feeling that I should use it for primary key only.

2) Should I create a table for parent companies? I wish to add information about those parent companies for example their headquarters. My struggle is: should I refer the parent company by its ID (if I create this table) or by its name? And in the parent company table should I put a field for all the companies in it? Or should a query be enough to list it?
I don't know if it's clear but here's 2 possibilities I'm thinking of :
- A field name "CompaniesID" in the tblParentCompanies with "102,380,942" which refers to 3 companies that belongs to the parent company.
- A query looking for companies with the "PC" field equals to "52" for example?
Which one is the best? And why? I think the 2 could be useful but I'm really new to Access!

3) Exact same question with a business developer table?

I have a lot of questions flowing through my head but I think they're all almost the same: should I double the information or just link it and do queries?

I hope my questions aren't too silly and that I'm posting it in the good section as it's more about general design.

Thanks,

Kind regards,
RaSk

 

[James Deckert]

1) Not aware of a standard, you don't even need it for the ID if you don't want, but sometimes it's easy to keep track of in queries and such if specified.
2) >>ID (if I create this table) or by its name?
ID, not name
>>should I put a field for all the companies in it?
no, the child companies refer to the parent, not vice versa.
>>tblParentCompanies with "102,380,942"
no,no,no - I think my previous answer covers the rest of your questions.

You need to read about table normalization before you go farther. Find a tutorial. It's very important you get the design of the tables correct or everything else will be harder than it should be.

 

[spikepl]

IS your business so totally unique that nothing satisfactory exists already? Cmmercial or open source? Really?

Have you scoured the net for Access templates, that perhaps cover 50 or more % of the needs and can be built upon? That is so much better, especially if you are a beginner ...

Own development is mucho mucho more expensive than people think, especially by amateurs (meaning, people who do not normally make a living in this field). Whatever ready-made you can find - use that for starters. And many beginners discover Access and want to rebuild the world in Access, no matter the at times insane costs of doing so.

 

[jdraw]

Rask,
Is this really happening in a viable business today? How do they really know their financial position at any time? Sounds like chaos.
I agree with spike --look at commercially available, supported products with customer user groups.
Hopefully , updating the commercial management system doesn't refer to SAP or similar.

Perhaps you could describe that to us.

 

[RaSk]

Thank you for your prompt replies !

I found a really interesting book that I'm reading right now : Access 2010 - Beginning database design. That answers a lot of my question !

spike> Hmm I didn't thought about downloading templates, I already found some examples during tutorials that match some of my needs but now that I'm trying to start it's a bit more difficult.

spike&jdraw> No my business is not that unique and we have already some big software within the group (like Sage ERP and stuff like that). Maybe I need to present a bit more what we do!

Basically we sell a service for boats, each boat have an owner who may be part of a bigger group (a parent company).
We have different invoicing system (monthly, quarterly, yearly and some exceptions), some owners wants one invoice for all their boats and some want a different for each, it depends on the contract.

What is really happening from each colleague's view :
- The manager wants to monitor the invoicing, invoices overdue, forecast for next year and an overview of our clients
- The business developer is in charge of managing our local distributors (they sell for us), they can also bring clients directly managed by them. Most of the time they are the ones who deal directly with the clients or with the distributor. They are developing business, so they bring contracts to the assistant, reports problems, forecast coming markets (by entering prospects into the forecasts for example) etc.
- The suspension guy is the one who can suspend the boat license if there's a problem (ship maintenance, fire, sickness, etc.) and usually it's took into consideration by the assistant to not invoice during suspension.
- The assistant who deals with adding new clients, invoicing them, monitor overdue and also give the commission to the distributor.

We are a little business unit within a group of more than 500 employees. To give you an overall idea of how it's working :
- Sage ERP where the assistant put the client information, financial stuff, and it prepares the invoices and inform the assistant of overdue problems. The output is a web page and an excel file for the overdue.
- Different forecast files, Excel spreadsheets mainly used by the manager and the business developers, I found 3 different files and one of the business dev refuse to put it on the server, so he uses his own copy locally...
- Lots of invoicing recaps: an excel spreadsheet where the assistant highlights the invoices sent and forecast the ones to be sent. When they send the invoice through the ERP, they put the invoice number and the information in this spreadsheet. They have at least 5 different ones, one for each continent, and the reason why is that they are 3 working on the invoicing so they cannot open the same Excel file. There is also a lot of comments and information, this is where a suspension can be notified for example.
- A suspension recap file but used only by the suspension guy and stored locally.. We use the mail (when the assistant is in cc :banghead to retrieve this information.
- Some overdue recaps files sent to our distributors (it's in their contract to help us with overdue). The assistant tries to synthesize the output file from the ERP and add some columns in the Excel file to send it to the distributor. The distributor adds its comments and returns it to the assistant.

If you read everything, you can image how much problems it can cause..
- Only assistants knows how to use the ERP, to be honest it's not at all user-friendly and I know why our manager hates when he has to use it.
- Forecast files are incomplete and a lot of information is not shared between business devs...
- Different invoicing recaps with different colors, standard of entering the data. It's a mess honestly! When we have to study it during commercial meetings an assistant has to be here to help us to read the data..
- The suspension recap you can imagine what happened.. We have a distributor which sends us activity recaps (because most of our suspensions come from them, they suspend for a month then use it again, it's the only distributor that does that) and it doesn't match to the suspension recap of our guy. But as it was emailed to our assistant, she invoiced "blindly trustly" (Idk it's a french expression translated) to the clients and some of them had a really good discount! They were using our services but reported as suspended so not invoiced.. We found out only this month after years of abuse from our asian clients!
- The last but not the least: the overdue! I'm just an intern in the company and I was really shocked by this number: 10% of our turnover is on overdue.. Some clients didn't pay during 3 years! The manager seems to have wake up this month and started to put a lot of pressure on assistants and business dev to do recaps and reports on that situation.

I'm just a business dev intern for 6 months and as my manager noticed my computer skills when I presented my worldwide market study (basically an Excel pivot table with most of potential clients in the world, he was impressed as he knows only a few things in Excel).

He asked me two things :
- Link the forecast file with the invoicing file so we can automatically see the active contracts for the next year
- Help the assistant with setting a good excel file to send to distributor for overdue
As he left for a business trip for 3 weeks, I wanted to a better job than linking two crappy excel files. I don't know how to say that in proper English but it's like putting a ribbon on crap.

I thought a bit about it and discussed it on a french Excel forum and the idea of setting up an Access database seemed to me as a good idea.
I just read in the book that setting up an Access system could be good for little business unit where the large ERP system used by the company could not fit the needs. I think this is exactly our situation.

Do you think it's worth it? I can learn quick and as far as I'm reading the book I really love it. I've done a bit of PHP and MySQL but just the basics and it was a few years ago! Now I use Excel daily and do a bit of VBA too.

Sorry for the long post and the story telling but I can tell from your answers that I'm in the perfect forum to develop my idea

Thank you very much,

Best regards,

RaSk

 

[RaSk]

Hi everyone,

Looks like my long post didn't convice you that my idea was not that bad.
I followed your advice and finished the book Beginning database design and I came up with a design that would help me with all my needs.

I'm now struggling with a many-to-many relationship.
How can I link end-users with their contract (including historicals) and their invoices?
Some clients want to be invoiced for several end-users but I still need to keep the information about the price detail..

I have made a UML diagram of where I'm at, feel free to give me advice on improving the design.

Thanks and have a good day

RaSk

 

[jdraw]

I read your post and have made some comments as follows:

- Different forecast files, Excel spreadsheets mainly used by the manager and the business developers, I found 3 different files and one of the business dev refuse to put it on the server, so he uses his own copy locally...

THIS IS A MANAGEMENT ISSUE.

- Only assistants knows how to use the ERP, to be honest it's not at all user-friendly and I know why our manager hates when he has to use it.

THIS IS A TRAINING AND MNAGEMENT ISSUE

- Forecast files are incomplete and a lot of information is not shared between business devs...

SEEMS LIKE SOME REDESIGN TO GET A COMMON FORECASTING MODULE (WITHOUT IT IT'S SILO DEVELOPMENT)

- The suspension recap you can imagine what happened.. We have a distributor which sends us activity recaps (because most of our suspensions come from them, they suspend for a month then use it again, it's the only distributor that does that) and it doesn't match to the suspension recap of our guy. But as it was emailed to our assistant, she invoiced "blindly trustly" (Idk it's a french expression translated) to the clients and some of them had a really good discount! They were using our services but reported as suspended so not invoiced.. We found out only this month after years of abuse from our asian clients!

THIS IS A PROCEDURES/PROCESS ISSUE WITH NO ONE LEADING

- The last but not the least: the overdue! I'm just an intern in the company and I was really shocked by this number: 10% of our turnover is on overdue.. Some clients didn't pay during 3 years! The manager seems to have wake up this month and started to put a lot of pressure on assistants and business dev to do recaps and reports on that situation

THIS IS A MANAGEMENT ISSUE (UNTIMELY DATA OR LACK OF AWARENESS/FACILITIES/TRAINING)

-I'm just a business dev intern for 6 months and as my manager noticed my computer skills when I presented my worldwide market study (basically an Excel pivot table with most of potential clients in the world, he was impressed as he knows only a few things in Excel).

YOU HAVE SEEN AN ISSUE AND UNDERSTOOD ENOUGH TO PUT THE ISSUE FRONT AND CENTRE WITH THE MANAGER.
SEEMS THE MANAGER HASN'T SEEN THE NEED FOR SUCH INFO PREVIOUSLY. A VIABLE BUSINESS KNOWS ITS FINANCIAL POSITION, AND HAS TOOLS FOR FORECASTING AND "WHAT-IF"ING. SEEMS THIS MANAGER HAS JUST NOW SEEN THE LIGHT.
BUT HE HAS SEEN 1 THING AND THERE ARE OTHER THINGS AND OTHER MANAGERS.

-I thought a bit about it and discussed it on a french Excel forum and the idea of setting up an Access database seemed to me as a good idea.
I just read in the book that setting up an Access system could be good for little business unit where the large ERP system used by the company could not fit the needs. I think this is exactly our situation.

Do you think it's worth it? I can learn quick and as far as I'm reading the book I really love it. I've done a bit of PHP and MySQL but just the basics and it was a few years ago! Now I use Excel daily and do a bit of VBA too.

YOU ARE BASICALLY DESCRIBING DATA MANAGEMENT/INFORMATION MANAGEMENT ISSUES OF A BIGGER MANAGEMENT ISSUE.

IF YOU ARE A 6 MONTH INTERN, YOUR CHANCES OF SUCCESS ARE MINIMAL. PROFICIENCY IN EXCEL IS NOT NECESSARILY A SKILL TRANSFERRED TO DATABASE(DIFFERENT CONCEPTS/DIFFERENT OBJECT MODELS).

BUILDING ADDITIONAL DATABASES IS NOT NECESSARILY A STRATEGY FOR LONGER TERM. YOU MAY WANT TO CONSIDER IDENTIFYING AREAS WHERE BETTER FORECASTING OR COMMON FORECASTING MODULES COULD EFFECT BETTER FINANCIAL INFORMATION MANAGEMENT. SUCH MODELS MAY EXTRACT DATA FROM THE ERP SYSTEM AND MAKE IT AVAILABLE WITHIN FORECASTING MODULES. YOU MIGHT CONSIDER MONTH END, QUARTER END, YEAR END "POINT IN TIME" DATABASES FOR MANAGERS (AND/OR OTHERS).

MY GUESS IS THAT MOST MANAGERS ARE NOT AWARE OF

" 10% of our turnover is on overdue.. Some clients didn't pay during 3 years!"

THESE ARE SYMPTOMS OF MANAGEMENT ISSUES.

I saw this after responding to your post.
I think the organization you're describing is in the middle of this:

A spreadmart (spreadsheet data mart) is a situation in which a company's employees has inconsistent views of corporate data because each department relies on the data from their own spreadsheets.

Because spreadsheets are easy to work with, many businesses rely on them to take the place of a data mart or data warehouse. Instead of providing a consistent view of the entire company's data, however, spreadsheets have unique views of limited amounts of data.

It is up to the spreadsheet creator to decide what metrics are important, what data needs to be collected, how the data is formatted and what level of granularity is required. In such a scenario, the spreadsheets become isolated data silos.

 

[spikepl]

Inspired by jdraw's post, I want to build on his "management issues":

You have a company in business, with a mess in a number of areas. That this has been allowed to persist can be due to innocent lack of knowledge.

But it could also be due to empire-building hindering dissemination of knowledge or data. In that case your db would get torpedoed right away. I've seen things like that happening, because a db would share knowledge that people so far held onto locally, and thus would shift the balance of power. Insane, I agree, but that is how such things might work. The key players touched by the db must be on board for the development, and their interests must be recognized and safeguarded or replaced by something else. Google "stakeholder analysis"

 

[RaSk]

Dear jdraw and spikepl,

Thank you for your replies.
You're absolutely right. There are a lot of managing and process issues, and I don't think that could be solved only with a database designed by a beginner.

The point is that I've been designated to do this task, to redesign the "commercial managing system". My goal is not only creating the database (or any other solution) but also write very clear process/instruction on how to share/store the information and avoid those problems.

I've thought of clearing all the Excel files and linking them but when I discussed it over an Excel forum they all told me to do in over Access. I'm a bit lost here.
I know that everything could be done on Excel (maybe have to think about the multi-user issue) but is it really not worth it to do it on Access?

As far as I'm discovering Access I really love it and I like to learn new things. If a solution on Excel is really better I will do it but could you consider this as a homework/personnal project? I'd like to give it a try. Now that I've read the whole book I don't want to give up now

@jdraw: Hmm that looks really interesting. One of the suggestion on the Excel forum was PowerPivot but it's not free and we have no budget. But I will have a closer look!

@spikepl: I don't know if I understood correctly (my English is bad), correct me if I'm wrong: sharing all the information through a database may cause power balance issues between employees (insane but can happens!). And that I should be attentive to each stakeholder needs to design correctly the db? So far I've talked with every employee who could be using those files and everybody loved the idea, told me their needs and issues with the current system. I'm doing it for them, they will be using it.

Thanks a lot for your time, I know it was a really long post

RaSk

 

[jdraw]

You're most welcome for the comments. I want to reiterate my concerns based on your latest post.
You are trying to solve a management issue with some software and procedures. This is not likely to succeed regardless of how much effort you put in, but you get my support for intent.

I don't know SAGE ERP , but have dealt with some SAP and Oracle installations. The successful ones have had a leader from the top - a CIO or strong manager who can see the "company" (not just a division or process) and has a drive for improvement. This improvement is really a better overall "company" -timely information, consistent information, repeatable processes, common approaches to problem solving, shared facilities/tools, lots of training and participation and a better shared processes and information.

It seems to me that the SAGE ERP has been misused or misunderstood. It is not being used for the business but for a part or parts Finance??? It is not fulfilling its role. That is a warning sign. When divisions/groups build their own independent solutions, they are adding to the "overall problem" known as silos. When silos don't talk, and can't share information, that's when senior management gets nervous. This stuff affects the bottom line. Maybe not today or tomorrow, but it is in decline until someone maps out the issues and potential solutions.

I wish you well. You are on the right track. But I fear a 6 month intern has to "overcome the way things are done", "prove beyond reasonable doubt that your approach is correct", then "get everyone to agree" and develop to a longer term plan. All that with

we have no budget

is not looking too promising.

Good luck. Please keep us up to date with the project.

 

[spikepl]

Maybe our reading of the situation is entirely wrong. But if you are "just" a stagiaire, then you could feign total ignorance of, and show total disregard for, such issues and just get cracking.

I had a peek at your diagram: that's a lot of stuff. I'd recommend using the agile approach for project model: split it up and make commitments to small workable proven and verified bits, prioritised after after how much they would benefit the company and risk (i.e. how sure are you that you can get that thing done with time and effort envisaged). Huge benefits first, huge risks first.

 

[RaSk]

Hello,
Thanks again to both of you for your replies

@jdraw: My colleagues and I agree 100% with you. I plan to organize a meeting to present the new system and it will be the good moment to discuss about managing and process issues and how to fix them.
I can understand why you want me to use our existing ERP but for the scope of the project we need to forget about it. Just let's say we have an invoicing software that produces invoices number and some other references, I just want to have those references in my database, nothing more nothing less.

@spikepl: I don't know how is it in the other countries but in France there are mostly 2 types of intern: the ones who make coffee and the ones who do harder job than the actual employees. I drink a lot of coffee but I'm bad at making it! This project may be one the last opportunity during my internship to show how important I can be to the company so I really want to succeed on this.

I've made some progress on my vision of the database :

- Client: I really need to keep the information about business developer and distributor. Concerning the contact information, I could simplify it by doing a table only for "contacts" with a boolean "Is he at group level? / Is he/she part of the mother company?" and just a "Group name" field in the Client table that could be null. Is it a good idea? I will only loose the information about the mother company (like adress and stuff) but I will have only one contact table.

- End-user and suspension: Everything is okay I think.

- Invoicing and overdue: If we think about separately, it is okay. An invoice is linked to a contract (so I can get Client information, end-users information and also suspension information!) and an overdue only exists if there is an invoice, in that case unpaid. The table named "OverdueDistributor" is just an history of all the comments made by the distributor (or by us) about this overdue.
This seems perfect as if I select a client, I can list all invoices overdue, print the historical comments and also the general information (which enduser, what type of contract).

The main problem is now linking everything together through the contract table !

If I don't keep historical contracts, I can't enter previous invoices (I don't want to enter all invoices for the last 10 years, at least just the ones with overdue problems). Or can I avoid this problem by allowing a defaut value for ContractID as a foreign key in the Invoice table ?
Historical contracts are not critical for this project and it causes me more problem than it helps me!

Basically: a client have one or more contracts (a contract is only for 1 client), each contract have one or more invoices (an invoice is linked to only 1 contract). A contract is linked to one or more end-users through a "contract price detail" which is unique for each contract (it is just to separate the price from the common information of the contract, I don't know if it's a good idea).
My struggle here is to design a price detail table. I've seen things that looks like it, for example Order-products junction tables but I don't know if it suits the needs for the project.

Do you have ideas or suggestions?

Thanks and have a good day!

RaSk

 

[jdraw]

Rask,

I don't have real detail to offer. From my own experience, I can offer a few guidelines.
-You're lucky to have 100% colleague support. I don't think I ever had more than 60-70% of colleagues (non management levels).
-It would be beneficial to you and "the cause" if you had a "patron" (someone who has the "eye" of management and is generally well respected, and can help sell the concept/plan)
-Don't over promise!! and don't underestimate any costs/time (resources)!!
-Build a higher level data model showing the "big business" view.
-Clearly identify the area you want to focus on on the big picture model, then gradually introduce your more focused model. Make this gradually introduction of models with as little technical detail as possible. (First model almost cartoon like picture; progressively add some additional info)[Your audience does not understand 1 to 1, 1 to Many, Entities... so don't turn them off with too much techy stuff]
-Get a good communication plan set up. Practice it with colleagues, so your not left with questions you can't explain simply.

-If you need Contract info, and if that info is resident in the ERP system, then I strongly suggest you take a read only copy of that info into your "application/database" automatically -every time you open your application. This recognizes the ERP as the authority, and gives you the latest info. You can say you are not building an independent system.

Look at this for my Stump the model approach to improve communications.

Do not start by describing the new system. I suggest you identify a few weaknesses (ones that are evident to your current manager who is asking you to proceed) and some ideas you have to correct/overcome these. Then move to the more conceptually system, using some of the vocabulary/terms that are common to your audience.(You want them onside - not guessing at what to hell is he talking about?)

You get my vote for intent, but watch out for the "we don't do things like that","you're trying to change everything we know" and the " I already have more work than I can do" camps.

Good luck.

 

[spikepl]

I've personally witnessed over a period of 1½ years how stagiaires and their work were treated in a French high-tech company. Worse than the disposable Mexicans in one of the maquilladoras on the US border.

I hope your company appreciates your efforts more.

In any case, after all this BS - if you have specific questions then ask.