Starting my first database.. Pointers for this situation? (1 Viewer)

[thebatfink]

I am about to embark on a new Database (my first). I've armed myself with a few books and I am working from an old database which we are trying to "improve" so if all else fails I will simply add to the current in the same way it is built, but I'm asking myself the question, is the current the best way or is Access even the best tool for this.

The main purpose of the database is to produce documents pertaining to the operating conditions of a machine to produce a product.

We have many products (5000) and several machines (4, soon to be 8 hence this databse job now). The documents we generate are different for each machine (ie the information on them and the format) but each document of a particular machine is the same.
Mostly each product runs on just one machine, but occasionally we do have products which are the same, but run on more than one machine, and as such have different running conditions.

Things we record are like, temperatures, speeds, tensions, pressures, widths, gaps, specifications, material types, material widths etc. Some of these are common to all machines, ie references to specifications apply to the product and not the machine, but gaps temps speeds etc differ per machine.

Currently we decide which machine the product will run on, and then using the form for that machine, input all the conditions. We then generate a preformmated document which draws this information into the relevant boxes and store this as a hard copy which is issued to the machines each time the product is produced.
We use the hard copy becuse we have a document control system in place where four peoples signatures are required on the back of each document to say the conditions have been approved. (This is something I would like to somehow get away from so that only a soft copy is required but am unsure how this could be achieved).

Problems we currently have are our records are all indexed via the product number, this is preventing us from producing another document using this product number on a different machine if it already exists on another machine. I presume through poor thought when the database was concieved.

Around 8 people will use the database currently, half of those daily generate new records (concurrently at times). We are all on the same network and the database is currently only accessed from here by all.

I'm just wondering if anyone could give me some pointers or main points I should be considering as I begin to look at the porblem and how to structure the database.

Thanks in advance for any help or wise words you can offer!

 

[georgedwilkinson]

Read up on normalization. But temper it with the fact that data comes out of a production machine in a flat format.

Consider using a DBMS that can handle a high volume of inserts. It really depends on how fast your production machines are pumping out data, but Oracle and SQL Server have temporary areas to store incoming data before it is written into the correct table. Both can keep track of data not written in tables for hours/days until the write occurs, ensuring all your queries will be accurate from the instant of commit. I can't speak for the robustness of Access under the same conditions.

I have designed this system before (in Oracle) if you need a consultant.

 

[KenHigg]

My first cut at the tables would be as follows:

A table for machines
A table for products
A table for machine/product document masters
A table for production machine/product documents

Used as follows:

- You set up all your machines in the machine table
- You set up all of the products in the products table
- For each product/machine combination you set up a master machine/product document
- Each time you have a product run, you create a new production machine/product document copied from the master machine/product document table
- The table for machine/product document masters would be set up as follows (This is not normalized but would work):
a. a primary key field
b. a fk field linking to the machine table
c. a fk field linking back to the products table
d. x number of spec fields
(For example if the maximum number of data points you wish to collect on any machine is 20 then thats how many spec fields you need)

So to set this table up you create a rocord for a machine/product and enter the relevant data in fields b and c. Then if you want to collect something like the machines hours to date you enter that as spec field 1. And then continue on with the rest of the specs.

Now when you create a new product run, it copies the specs from the master table over into the run table. Then the final pc of the puzzle is in the run table, in addition to having spec desc 1, etc which is copied from the master table you also have a spec 1 data value which gets entered after you survey/inspect the machine.

So you can kind of see where this is going ?

 

[thebatfink]

Thanks for the replies. Unfortunately alot of that goes over my head, so no I dont really see where its going I think more reading is in order.

Also I'm not sure if you understand what it is we are doing with the database..

The database and information in it is purely to create a report in the form of a document which gives operators the conditions to set the machine too. Nothing more. Obviously each time a specific product is run, it needs to be run under the same conditions as the previous time to ensure consistancy and repeatability.

The document we supply them with will be

Product: A
Machine: 1
Temperature 1: 90
Temperature 2: 100
Temperature 3: 6
Pressure 1: 25

etc etc (that is very simplified obviously).

Now this data for temps and pressure are in no way related to anything other than this specific product running on this machine. If this product runs on a different machine, all values will likely to be different. A different product on the same machine will also have completely different settings too.

Each entry that is made is entered manually with no relation to any other entry currently in the database. We purely use it as a way to enter data, which can be formatted into a 2 page document automatically with the ability to find that document and make changes if need be in the future.

Thats why I wonder if access if suited because we dont ever query for specific fields or anything, its just purely to make these documents up and have an electronic store. Its nice and easy because you are presented with a form from the switchboard, which when you fill it out, you can then search for the product number and generate a nicely formatted document.

 

[KenHigg]

Ah... I see. Yeah I did over complicate it then. I don't see why you don't simply have a main product/machine. Then have a products table and a machine table. So in the main table you have a primary key field. Then a product field which is populate via combo box from the product table. And the same with the machine field. Then have say 'setting name field' 1 through 20 and 'setting field value' 1 through 20. For each product/machine you go in and set up the values as needed. Again this is not normalized but as long as you're not talking thousands of records it really doesn't matter...(IMHO )

 

[KenHigg]

I've been grilling over this and have a question: I'm guessing a machine has a finite set of settings. Let's say machine x has 100 possible settings. Will all products produced on that machine need a value for all 100 possible settings or is it possible that a product need only have specific value for 20 settings?

 

[thebatfink]

Each SOC (record) we generate for a machine has the same set of settings, and all must be completed, just with different values each time.

Theres currently around 30 fields for each SOC which must have information entered.

The fields although different for each machine, remain constant accross from one record to another for that particular machine.

There are also several thousand SOCs (records) split between the machines. Say 5k, which is set to double.

Ive read on here people saying you can have 2 databases, one front end and one back end? WHat are the benifits of this? Ive found that although we only have around 5k records now, and the database is only around 30mb, its become very slow.

Its almost as if there is a seperate database for each machine, but they need tying together somehow?

Thanks for your time by the way, much appreciated!

 

[KenHigg]

Let me get back with you on this tomorrow...