Recommended Table Structure for Facilities Database 3

[anymouse]

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.

 

[lameid]

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).

 

[MajP]

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.

 

[anymouse]

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.

 

[lameid]

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.

 

[dhookom]

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[/LINK]
IMO, A normalized table structure means a lot less work building and maintaining the system.

Duane
[url=http://www.access.hookom.net/]Hook'D on Access
MS Access MVP

 

[MajP]

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.

 

[anymouse]

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.

 

[dhookom]

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

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

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.