Project Database Design (1 Viewer)

jjake

Registered User.
Local time
Today, 18:35
Joined
Oct 8, 2015
Messages
291
hello,

I have an existing database that i currently use to store detailed information on hundreds of machines, instruments and other equipment. This is stored in [tblEquipment]

I am building a new project management database for installations of new equipment/machines in order to assist me in keeping track of the project from start to finish, this includes budgeting, purchasing and data entry for new equipment.

My Idea.

I have a main navigation form which uses several tabbed forms/subforms for my project. One particular form/table i use to keep track of equipment i need to purchase to install. This is a continuous form [frmProjectPurchases] that lists all the equipment. This goes into [tblProjectPurchases]

I would like to add another form [frmEquipmentDataEntry] that allows me to pull all the equipment i purchased from [frmProjectPurchases] but will allow me to input all the details on said equipment into [tblEquipment]. I was thinking of scrapping [tblprojectpurchases] and putting all this into [tblequipment] but alot of purchases will not actually have any details saved for them since they are redundant parts.

What would be the correct approach?
 

plog

Banishment Pending
Local time
Today, 18:35
Joined
May 11, 2011
Messages
11,613
I recommend seperating user interface from table structure, especially in your mindset. Your explanation merges the two. You need to first conquer the data (tables), then move to data extraction (reports/queries), then finally work on the user interface (forms).

Start this by opening a blank database and building tables to accomodate the data you will put into it. Then, open the Relationship tool, bring in all your tables, set up your relationships, expand the tables so you can see all the fields in them, take a screenshot and post it here so we can help you validate the structure.
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:35
Joined
Jan 23, 2006
Messages
15,364
jjake,

It seems you are working with physical database and user interface options. As plog has suggested, you really have to get your tables designed to meet your business needs.
Here is a link to Database Planning and Design info - I recommend you work through a few of the tutorials from RogersAccessLibrary mentioned in the link.

Many find that starting with an overview description of the business --the 30,000 ft level - then gradually add detail is great for analysis and clarity. Build a model (pencil and paper). some test data and scenarios--revise until it meets your needs. You'll have a blueprint for building your database. Designing as you go ("on the fly") is rarely productive.

Good luck with your project.
 

jjake

Registered User.
Local time
Today, 18:35
Joined
Oct 8, 2015
Messages
291
I recommend seperating user interface from table structure, especially in your mindset. Your explanation merges the two. You need to first conquer the data (tables), then move to data extraction (reports/queries), then finally work on the user interface (forms).

Start this by opening a blank database and building tables to accomodate the data you will put into it. Then, open the Relationship tool, bring in all your tables, set up your relationships, expand the tables so you can see all the fields in them, take a screenshot and post it here so we can help you validate the structure.

This is a very basic layout from my main database. See attached.
 

Attachments

  • Relationships.JPG
    Relationships.JPG
    55.7 KB · Views: 91

plog

Banishment Pending
Local time
Today, 18:35
Joined
May 11, 2011
Messages
11,613
First, no need for tblYesNo. There's an actual Yes/No (True/False) field type in Access. Use that for those field types in tblProject"Purchase instead of tblYesNo.

Second. Give me 2 explanations. For the first, pretend its career day at a local elementary school and you are telling the kids what it is your orgainization does. No database jargon, explain any industry jargon and keep it simple.

Then give me a high level explanation of your database--light on the jargon. Tell me what data you have, where it goes and what the point of the whole thing is.
 

jjake

Registered User.
Local time
Today, 18:35
Joined
Oct 8, 2015
Messages
291
1. My company manufactures goods for the oilfield. We use numerous machines throughout the process. We also install new machines ourselves to further our capabilities.

2. My database is used for numerous reasons,

Purchasing of new equipment [tblPurchasing
Details of owned equipment [tblequipment]
Preventive maintenance of equipment [tblPM]
Repairs/Work orders of equipment [tblWorkOrder]
New investments in order to further business growth [tblProject]
Details of investments [tblprojectdetails]

I am trying to create a section of my database that will allow me to keep track of each investment/project from start to finish involving

Budgeting
Scheduling
Purchasing
Database Tracking This would involve the section i inquire about. I want to be able to create a list of equipment i need to purchase, Keep track of what is purchased and received then make sure the equipment i have received has had the details of it input to the [tblequipment] table.

Too many times will we receive new equipment, install it in the field and the data entry gets put on the back burner. Next thing you know no one has the details for it so they have to go and fetch it off the machine in the field (sometimes in a very difficult spot to reach), or the preventive maintenance gets left undone because it never got put into the database schedule.

This section will ensure that the following steps are followed,

List of purchased equipment has been completed and received.
Equipment details have been entered
Equipment Preventive Maintenance items have been input to [tblPM]

Only when these details have been entered will the project truly be 100% complete.
 

plog

Banishment Pending
Local time
Today, 18:35
Joined
May 11, 2011
Messages
11,613
I don't think you need tblProjectPurchases. Using the image you posted as reference, you should be able to just put tblEquipment in tblProjectPurchases place, add to it the fields from tblProjectPurchases.
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:35
Joined
Jan 23, 2006
Messages
15,364
jjake,

Here are a few questions that may help in scoping your project and designing the database.

Code:
What equip do we have currently
What equip do we need to purchase 
What equip have we purchased
When was the equip  purchased
From whom was it purchased
When was equip received
When was equip installed
Who installed equip
Where is the equip installed
What maintenance has been done to equip
What is preventive maintenance schedule for equip
What equip is planned for removal,replacement,disposal..

Part of the not knowing what was installed or when or where could be because your business processes are not matched/part of your proposed database/data entry. For years there was paper work (orders,deliveries, installations, maintenance...) done by different groups in different locations and each area tended to be a silo unto itself. In order for info to be current and accurate, there must be checks and balances within the business process(es) and related database activities to ensure data is captured and stored when necessary. Database to support the business and business processes are not independent.

Look at the bigger business picture --model it with a diagram however conceptual -- this will help keep the major functions and their requirements in focus. If you don't know some details, simply add a black box to your model and include some key point of what's in the box. Do it early so you don't forget or overlook that requirement. Temper your development based on priorities of management. Vet the model with the planners, purchasers, installers maintainers.....

A database, such as what you describe in your posts, requires planning and design with input from all the players. The more you model and communicate and refine your analysis and involve others who will be expected to use the database, the smoother will be the development and uptake.

Good luck with your project.
 

jjake

Registered User.
Local time
Today, 18:35
Joined
Oct 8, 2015
Messages
291
I don't think you need tblProjectPurchases. Using the image you posted as reference, you should be able to just put tblEquipment in tblProjectPurchases place, add to it the fields from tblProjectPurchases.

Thanks Plog, this is what i had initially thought of but i wanted a second opinion

I was thinking of scrapping [tblprojectpurchases] and putting all this into [tblequipment] but alot of purchases will not actually have any details saved for them since they are redundant parts.
 

jjake

Registered User.
Local time
Today, 18:35
Joined
Oct 8, 2015
Messages
291
jjake,

Here are a few questions that may help in scoping your project and designing the database.

Code:
What equip do we have currently
What equip do we need to purchase 
What equip have we purchased
When was the equip  purchased
From whom was it purchased
When was equip received
When was equip installed
Who installed equip
Where is the equip installed
What maintenance has been done to equip
What is preventive maintenance schedule for equip
What equip is planned for removal,replacement,disposal..

Part of the not knowing what was installed or when or where could be because your business processes are not matched/part of your proposed database/data entry. For years there was paper work (orders,deliveries, installations, maintenance...) done by different groups in different locations and each area tended to be a silo unto itself. In order for info to be current and accurate, there must be checks and balances within the business process(es) and related database activities to ensure data is captured and stored when necessary. Database to support the business and business processes are not independent.

Look at the bigger business picture --model it with a diagram however conceptual -- this will help keep the major functions and their requirements in focus. If you don't know some details, simply add a black box to your model and include some key point of what's in the box. Do it early so you don't forget or overlook that requirement. Temper your development based on priorities of management. Vet the model with the planners, purchasers, installers maintainers.....

A database, such as what you describe in your posts, requires planning and design with input from all the players. The more you model and communicate and refine your analysis and involve others who will be expected to use the database, the smoother will be the development and uptake.

Good luck with your project.

Jdraw, thank you for your input.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:35
Joined
Feb 28, 2001
Messages
27,001
You are in a design phase. I'm going to pull out my old stand-bye advice along with explanations.

Old Programmer's Rule #1: If you can't do it on paper, you can't do it in Access.

Translation: You are building a data/procedural model of your business. You need to have explored the business well enough to be able to specify the model's content. If you do not have a good description of every entity you want to track AND how the different entities interact/interrelate, you are going to have trouble.

Most of the time, I give advice of building a "project bible" that is your reference not only to how your business works but how your model will track/simulation the process. This is a document that will be where you go when you need to know how to implement something program-wise. I think of it as a road-map and the analogy works like this: How will you ever know where you are going without a map? How will you ever know that you have arrived if you don't have a map?

The trick is to identify each entity. Now, what do I mean by entity? You talk about specific bits of equipment. You were talking about tblEquipment. Your equipment would be one entity.

You have a project. You want to associate a project with a piece of equipment purchased for that project (according to your questions about your diagram). The project would be an entity. Not quite as hard/physical as a piece of equipment, but easy enough to see.

Sometimes you make an association between two entities, which I think MIGHT apply in your case. If you allow the equipment to stay with the customer whose project paid for it, that is one model. If you keep the equipment and can reassign it to another project when the first one is finished, that is a different model. But in EITHER case, the assignment/association of equipment to a project is a logical entity.

When laying out things like purchase orders, vendors, shipping manifests, etc., each of those can potentially be an entity. When you can identify all of those entities and how they relate to your business, you are well on the path to solving your problems.

At the point where you can identify entities and how they relate and how you process each one at each step along the way (even if only in broad-brush terms), you have a design document worthy of consideration.

Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first.

Translation: As part of #1, you probably defined forms and/or reports you wanted to see in the finished product. (Part of the "how will you know when you have reached your destination question.) For each displayed or printed item, for each result that you store, for each value you want to develop, you need to verify that you are in fact capturing the data needed so that Access CAN tell you what you want to know.

If item X is elementary, verify that you have an input of X. If item X is computed, verify that you have a data source that feeds the computation. If this sounds to you like you would sometimes be working backwards to be able to answer the question, you WERE paying attention and you got it right.

You must remember that Access is like a power tool for the mind. You are the architect. Access is not the designer. All it knows is how to make tables, queries, forms, and reports. You are the subject matter expert. That is why you must apply the rules to verify that what you are building WILL do what you want it to.

Do not despair. It seems like building that project bible will be a pain in the toches - and you know what? You are right! But once you get the hang of it, it starts moving along very nicely.

One MS Office tool that might be helpful is Word. It has an OUTLINE mode in which you can build a formal multi-layered outline to drill down on what you design. You can make it show you everything on a given layer. You can print the outline at will. And yet it is trivial to edit.
 

Users who are viewing this thread

Top Bottom