×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Recommended Table Structure for Facilities Database
3

Recommended Table Structure for Facilities Database

Recommended Table Structure for Facilities Database

(OP)
I am creating a MS Access 2003 database to manage information about 300 facilities.

Each facility has 500 capabilities associated with it.  For example, FACILITY_001 might have:
  CAPABILITY_001=Y
  CAPABILITY_002=72
  CAPABILITY_003=Steady State
  And so on...

Additionally, each capability for each facility has a unique note associated with it.  For example, FACILITY_001 might have:
  CAPABILITY_001NOTE=Yes, however, this capability will be re-examined for this facility in 2018.
  CAPABILITY_002NOTE=72 is typical, but this facility sometimes sees as low as 68
  CAPABILITY_003NOTE=Steady State project completed Nov 2007
  And so on...

Originally, my idea was a large, fairly flat table; however, that will quickly run into the 255 field limit.  Breaking it up into multiple tables seems doable, but the information is 300 unique facilities with 1000 unique bits of information about each facility, so I'm a little stumped as to the best way to handle this.  Hopefully the answer is simple and I am just overlooking it by being too close to the problem.

Please chime in if you can recommend a good way to design the table structure for this database.  All help is appreciated.  Thank you.

My database experience is limited and sporadic.  In past years, when a database was needed for something, I created one using tables that were either flat, first normal form, or second normal form.  Although I only recently learned about database normalization, I have apparently been designing most of my databases this way all along.

RE: Recommended Table Structure for Facilities Database

I would recommend you keep reading about data normaliztion.

It seems as though you should have 3 tables.  
Site (list of sites)
Capability (list of possible capabilities)
Site_Capability (list of capabilities at a site)


The Site_Capability table would have three fields...

Site_ID Or other relevant foreign key to the site table
Capability_ID or other relevant foreign key to the Capability table
Note


I am assuming your 'capabilities' are not literally named the way you have them...  If so I would take this all the way back to determining what the application is to do supported by Business rules not just because it has always been that way (sometimes that is the Business rule).
 

RE: Recommended Table Structure for Facilities Database

Besides a Swiss Army knife I doubt anything can be uniquely described with 1000 attributes.  If it could, a single table would make sense (although it would exceed the field limit).  So can you explain a few of these capabilities.  My guess is there are a lot of child records transposed into capabilities.

If you had an actual 1000 field table, would mostly every field be filled in for each record?  Or would you have a lot of empty fields? This helps in getting a sense of how normal.
 

RE: Recommended Table Structure for Facilities Database

(OP)
Thank you for your responses.

I will keep reading about data normalization.

The names and numbers were simplified for clarity.  Capability_001 could actually be AvgCyclesPerDay.  Capability_002 could be MaxLoad.  Capability_003 could be RPXCapacity.  Capability_004 could be StagingMethod.  And so on...  The exact terms are irrelevant, but there are several hundred unique descriptors (a combination of boolean, numbers, and text) that help to describe every facility.  The information is used to support business decisions.  There may be an exception here or there, but essentially all capabilities/descriptors are used for each facility.

This isn't the best analogy, but I think of this like a personnel database containing information about 300 people where each person has a first name, a last name, and 998 unique middle names.  It's odd, but if it existed this way, you'd have to come up with some way to handle it.

It would definitely be easier, at least in my case, to simply make a 1000 field table.  With only a few hundred facilities, any future growth and management of the database would be practically a non-issue.  Unfortunately, even Access 2010 only supports 255 fields max, so I guess that's some kind of a magical limit for database software, which is designed to handle many thousands of records and not just 300.

RE: Recommended Table Structure for Facilities Database

If all the sites have all those attributes then one table is in theory appropriate, if not practical.

I guess the real world question is how are the attributes used together....

Do you need to display all 500 at once or do you only need certain groups?

You have little choice but to somehow group your fields into tables unless you can somehow easily get to the information you need with attributes in a related table.

RE: Recommended Table Structure for Facilities Database

When I see "500 capabilities associated with it" I always assume there will be a 501 and 502. If your capabilities are fields then are you going to change your table design?

You might want to consider a table structure like At Your Survey
IMO, A normalized table structure means a lot less work building and maintaining the system.

Duane
Hook'D on Access
MS Access MVP

RE: Recommended Table Structure for Facilities Database

This is what I believe Duane is suggesting because it mirrors some of the structure of his survey database. Duane, correct me if wrong. But I think it requires some explanation because there are some non-standard tricks to do this.  

The trick is how to handle mixed data types in the structure Duane proposes.

tblCapabilities
  capID           (Primary Key, I would just use autonumber)
  capDescription  (text, something like "Max Cycles")
  capValueType    (Identifier if text, boolean, numeric, date)
  capUnits        (if numeric what units "hz", "rpm" , "count", "pallets")

example

1  Max Load         Numeric Integer   Tons
2  Staging Method   Text      

tblSite_Capability
  siteID (foreignKey to site table)
  capID (foreignKey to capability table)
  capValue (this is a text field to handle mixed data)

example
1  1  50
1  2  Warehouse


The above says
 site one has max load of 50 tons
 site one has staging method of in warehouse

This is the trick to having a single field to handle mixed data types. However you know what data type it is and you can use code and queries to still do mathematical calculations.  

The other trick is to modify the table
tblSite_Capability
  siteID (foreignKey to site table)
  capID (foreignKey to capability table)
  capValueText (if text goes here)
  capValueLong  (if long value goes here)
  capValueDate  (if date goes here)
  capValueBoolean (if boolean goes here)
  capValueFloating (if floating point number here)


Both ideas work and are more flexible and efficient than a large table.  However, a purist may say that neither is truly normal. The first concept is storing multiple data types in a single field, the other has multiple fields to store a single data element.  In truth the more normal solution is simply two large tables linked by site ID.  255 fields in the first and 245 fields in the second.  In praticality this would be really painful to manage.

I would put notes in their own table that for sure is more efficient based on how Access handles notes.
tblNotes
  noteID (pk)
  memoNote (memo field for the notes)
  siteID_fk (composite key linking a note to a specific capability in the
  capID_fk   tblSite_capability table

your thoughts.

RE: Recommended Table Structure for Facilities Database

(OP)
lameid, I will probably try grouping the fields (each group of capabilities gets its own table).  At my skill level at this point, that will definitely be the fastest way to getting a database done and ready for use.

dhookom and MajP, The At Your Survey database has given me some ideas, and MajP's comments do help my understanding too.  Longer term, I may re-design the database to be normalized and  customizable by regular users.  That would be a better design and a good learning experience for me in the process.  And dhookom, you are right about capabilities being added in the future--that is inevitable.

At this point, I appreciate all the ideas that will help get this started, both in the short run and longer term.  Thanks for all the help.

RE: Recommended Table Structure for Facilities Database

MajP,
Thanks for taking the time to accurately explain my sometimes brief replies.  

Duane
Hook'D on Access
MS Access MVP

RE: Recommended Table Structure for Facilities Database

anymouse,

Think of your table layout as the foundation layer of your house.  If it is done incorrectly and has to be changed or fixed later it is the hardest change as it has the possibility to impact everything else.

Ask yourself why each set of fields is grouped together?  How does that fit into normalizaton rules?  Are the fileds perhaps for the HVAC system (gas/electric; Boiler / Central AIR) and you should have an HVAC table?

Ultimately, data normalization is a skill like math.   Either you can do it or you can't.  So none of us can give you a certain answer of how you should best proceed for sure without digging into the problem completely to understand it.  There is also the art of balancing of Data design and application performance but I have never said this data is too normalized to get the result, only that this particular thing takes forever to run.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close