Designing a database for a small lab (1 Viewer)

[Steepleboy04]

I need help designing a couple databases.

I have 300 formulas to manage for our chemist. He wants to be able to veiw a formula code, name, and notes (this data would come from a Formula data table with 3 fields). He also wants components listed below with their respective code, name, price and amount (or parts).

I designed a form before creating databases to get a feel for what I need. It would display a formula code, name, and notes from the Formula data table. I need to be able to edit these textboxes and update the data in the Formula table.

I would then input and edit components (up to 10) and calculate total cost (and total parts).

I'm not sure how to set up and related two data tables to do this.

Any ideas?????

Formula Table (Formula code, name, notes, components1, 2, 3, 4, 5, ...10?)
Component Table (component code, name, price)

 

[Karma]

Hi,

This is my analysis of the problem you have. I have normalized the data - please look this up as it is fundamental to how relational databases work -
and shown you where i have made assumptions.

Formula Db

Key:
TBL_NAME
FIELD_NAME [Field Type; Key/index;Required]


TBL_FORMULA
FormulaID [Autonumber long integer; set to Index yes no duplicate;yes]
Formula_Code [Text?;Index yes no duplicates; yes]assumes unique in house generated code
Formula_Name [Text;Primary Key ;yes]assumes formula names are unique
Notes [memo;;No]

TBL_Formula_TBL_Components
FormulaID [Number-Long Interger; PrimaryKey; yes] related to TBL_FORMULA.FormulaID
ComponentID [Number-Long Integer; PrimaryKey; yes] related to TBL_Components.ComponentID
Component_Amount_Used [Number Double? is this a mass or volume?;;yes]

TBL_Components
ComponentID [Autonumber Long Integer set to Index yes no duplicate;yes;yes]
Component_Code [Text?;PrimaryKey;yes] assumes unique CAS number or similar
Component_Name [Text;Index = yes no duplicates ;yes] assumes component names are unique ;
Component_Price [Number-Currency;;yes]
Component_Amount_Sold[Number-Double;;yes]

This way you are not restricting yourself to a 10 components should a formula come along that is made up of 11 and you are not storing empty space for formulas that consist of less than 10 and you are summing your component data in rows rather than across columns which is an additional benefit of normalizing your data.

You'll have to consider that each component will probably be sold by a certain volume or weight and that each formula will use a % of that weight or volume therefore you'll need to consider a calculation in any queries you build using Component_Amount_Sold, Component_Amount_Used and Component_Price. If the amount used is a dilution of a solid then you should consider recording the component in it's dry state otherwise you'll need to think about adding a field to TBL_Formula_TBL_Components that records a dilution factor and using that in the calculation as well.

I am assuming that this database is purely for looking up values, if you want to record a history of when a formula was used, the price at that time and the components used and their proportions you'll need a transaction table.

Please be aware there may be legalities about using electronic medium to store data for use in experiments [GLP,GMP etc] or for pharmacetical dispensing to humans or animals.

HTH

K. [ex-chemist ]

 

[Steepleboy04]

I read an article on Normalization from the utexas ITS site to familiarize myself with the concept before I read your response.

I followed your structure and created three data tables. I made the Formula_Code field in the TBL_Formula a primary key, since this will be unique. Formula_Names may have some duplication.

A couple questions I have: I would like to cost out some of the formulas. Since this cost will be determined by a particular compenent and amount, then assigned to a formula, should I create a field on the TBL_Formula_TBL_Component ???

I am currently creating a form to search, update, delete and insert components, and another form for to do the same functions to formulas.

I do not know anything about subdatasheets, but I was thinking of inserting the TBL_Formula_TBL_Components datasheet into the TBL_Formula. Would this work??

Thanks for the input. Ex-Chemist indeed.

L

 

[Karma]

I read an article on Normalization from the utexas ITS site to familiarize myself with the concept before I read your response.

I followed your structure and created three data tables. I made the Formula_Code field in the TBL_Formula a primary key, since this will be unique. Formula_Names may have some duplication.

A couple questions I have: I would like to cost out some of the formulas. Since this cost will be determined by a particular compenent and amount, then assigned to a formula, should I create a field on the TBL_Formula_TBL_Component ???

I am currently creating a form to search, update, delete and insert components, and another form for to do the same functions to formulas.

I do not know anything about subdatasheets, but I was thinking of inserting the TBL_Formula_TBL_Components datasheet into the TBL_Formula. Would this work??

Thanks for the input. Ex-Chemist indeed.

L

3rd normal: An entity is said to be in third normal form if it is in second normal form and all non-key attributes are mutually independant.

In English that means that if you can derive a field from 1 or more fields in the same table it's not in third normal form. The most common reason for this is that a field is a product of a calculation based on 2 or more fields in that table.

That's a long winded way of saying you shouldn't hold calculated data in tables. The calculation can be done using a query or a -as an example - a text box on a form that holds a formula.

The best way to present this data is using parent and child forms.

I've attached a zipped example - it's not pretty - but it should give you a good idea of how to do this. There is a main form displaying the formula and a bound subform displaying the components of the formula and unbound sub form that lists the available components which you can add to - you could remove this and open it from a button when requried. It might be worth adding a units field to TBL_Formula_TBL_Components - would require a look-up table of units - as this would give a bit more info i.e. mg, g, mols etc.


Let me know how you get on with it.

K.

 

[Steepleboy04]

Karma,

I unzipped the file you sent me and I have spent a little time studying it in my quest to gain some skills and understanding in databases/access. (I'm a complete novice, but I am learning from your posts and examples).

My first question is a general question about database design. In what order did you design this database (tables, queries, forms, subforms, subdatasheets)?

I assume you start with normalizing your data into tables to get your initial structure (fields, data types). Then you create relationships between tables by using queries-this filters data and creates a query of desired data fields. From these queries you can design a form that uses both tables and queries as sources to input, update, calculate, delete, etc. data.

I am going to spend the next couple of days looking at the subforms and properties of the combo boxes and other objects to see how to use them for finding and editing data.

 

[Karma]

Hi,

It's good to see someone taking an interest in how to do this properly
It's obviously a large topic and not something i think i could cover myself but i can give you some pointers.

The first thing to do is analyse your requirements. The purpose of a data base is usually to store information about a system. Sometimes a system - a real world process or processes - will develop around a database but in the main the database is a model of a system that already exists.

There are a number of ways to model the system such as Structured Systems And Design Methodology [SSADM] but the aim is to indentify the boundaries of the process you want to analyse and the entities [objects/processes/data storage] that exist within the boundary and form the system. The outcome of the analysis should also give you the key entities and relationships between the entities. i.e. People buy Cars. 1 Person may by many Cars; one Car can be bought by many People [over time]. We've already indentified two entities People and Cars; 1 process Buying with an attribute time; and a relationship

The aim is to have a map of all the objects and processes that form the system, a logical model [Look up ERD]. For a large system this can have many many layers for something simple perhaps just one or two. It doesn't need to take long necessarily but it should be done at the start even if you are turning a spreadsheet based process into a database.

The logical model should model the real world and be independant of the platform - such as Access - that you will finally use. Be clear at this stage that if you are modelling an existing process then that's what your logical model should display; be as unbiased as possible about it. Try not to save the world at this stage.

Once you have your logical model you may decide that it shows you inefficiencies that can be looked at. This is a seperate process that can have wide implications for the business but is worth doing so that you don't end up implementing a database that needs changing late in the day when someone decides that your analyis indentifies issues. -systems always change but i've found this is a good point to get agreement that this is how the system works and that is what you are building your database on. If the system is changed at this point your logical model will also need to change.

Once you are happy that you have all your entities and the relationships between them you can start to look at the attributes those entities have. You may have already identified some that allow you to work out how the entities are related to each other but you'll want to analyse each entity and decide what other information you want to store about them i.e a customer entity will have a name age phone number etc.

When you have assigned attributes to your entities you'll then need to think about normalizing your entities.

You'll see that some of the data in the entities breaks 1st or 2nd or 3rd normal forms and that you have relationships between entities that require resolving with join/link/bridge -same thing- tables. You may find you need to create two entities from one in order to comply with normalization rules and you will need to look at unique indentifiers for your entities. [look up 1 to many]

When you start the normalization process you move away from the logical model to a physical model that may be biased towards the platform that you are intending to use. The normalization process will create structures that don't exist in the real world.

You'll perhaps realize that at this point we have created our tables and the relationships between them and primary keys - note there is a relationships window in Access that allows you to indentify these and enforce referential integrity and other things - look that up -

You can then decide which attributes must appear for the entity it exists in and what data type they are. Each attribute needs to be stored and will need a type e.g text, number etc.

Now you can think about creating your database

Tables first.
Then relationships.
Then queries and forms
Then queries and reports



It's not really that hard you already did it.
You defined your system boundary i.e. you only wanted a look-up system.
You indentified the entity Formulas. These have attributes one of which is Components. This entity then needed normalizing so you created two entities one for Formulas and one for Components. You then needed to resolve that further so i added the link table.

There is a lot to this and i'm not particulalry good at explaining what goes on in my head but i can recommend a really good book. Got me through college

http://www.holbornbooks.co.uk/details.aspx?sn=1250285

Database Design & Management [using Access] by Nick Dowling published by Letts

HTH

K.