How can I split this table?

[pa3329]

I am rewriting a db in access but I am not sure of a logical way to split this table. I have the following fields:
Generic Name, Chemical Name, CAS Number, Quantity, Container Type, Manufacturer, Health, Flammability, Reactivity, Personal Protection, Location, Comments, MSDS

It is a database which holds chemicals my company has on hand. All fields will be required. The health, flammability, reactivity, and personal protection are simple values (0-4 or A-X). This is all of the information which will be stored in the database so I feel that this table should be split but again I am not sure how it could be split.

Also, is it possible to put a link on a report that links to an outside document? This is what the MSDS field will be doing.

Thanks in advanced.

 

[rjoubert]

Why do you need to split the table? It seems that the fields you have listed all pertain to a chemical. If you envision having multiple records in this table for each chemical, then you may want to think about splitting the table. But if there's only going to be one record per chemical, you really don't need to split it.

 

[pa3329]

Thanks, that is what I thought but I am just thinking back to my college days and my database theory course. I can just see my instructor yelling because of how many fields I have in this one table, and that there is only one table in the database. I don't see any reason to split the table because there will only be one record per chemical but I thought maybe somebody else could.
Thanks again.

 

[Golom]

The table design mantra is

Every non-key field must be dependent on the key, the whole key and nothing but the key.

You have occasion to split the table only if

[li]A field does not directly describe an attribute of the key field (presumably Chemical Name in your case.)

[/li]
[li]It describes some attribute that characterizes only part of the key.
That really applies only to tables with multi-field keys.[/li]

[li]It depends for it's value on some other attribute that is not part of the key.[/li]

Far as I can see, none of those apply so there's no reason to split the table (yelling instructors to the contrary.)

 

[dhookom]

I'm going to yell if no one else is

I would not use fields like Health, Flammability, Reactivity, Personal Protection. I would create a related table with a structure and data like:
[tt][blue]
FKField Attribute AttributeValue
2 Health 1
2 Flammability 0
2 Reactivity 3
2 Personal.. C
3 Health 4
3 Flammability 4
3 Reactivity 2
3 Personal.. T
[/blue][/tt]
My Attribute field would actually be a primary key value from an attribute lookup table.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pa3329]

What is your FKField?

 

[Golom]

ForeignKey

 

[pa3329]

Ok, sorry for the dumb question but I have been out of college for many, many years and I have done nothing but firmware programming since then so creating a database is almost new to me.

As for my other question, can I put a hyperlink (to a file) in a report?

 

[rjoubert]

Create a label on your report, and in the properties for that label, put in a "hyperlink address

 

[lespaul]

check out the links below for a refresher course on normalization!



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[fneily]

I like your professor. If anyone has only one table in an "Access" database, what's the purpose? Just place the info in Excel and run autofilter or advanced criteria filters on the Excel LIST. You can even make an input form in Excel plus other things. But as you noticed from other responses, when you "normalize", you end up with more than one table.
You may also have extended info on container type, manufacturer, and location. So these would have their own tables.