Database Design for Specification Report

Question

Our group would like to store our specification data in a database. However, we need some assistance in the database design.

The group decided on an eight-page specification which has "blanks" for at least a hundred single values. Several sets of values are displayed in a tabular format. If a value is blank, it does not apply for the given product. Each field will have a particular data-type. Fields with the following data types are all represented: integers, decimals, lookup list, text, date, and boolean.

The database should also keep track of previous revisions.

From a general perspective, how should I design my database?

Option 1: One or many one-to-one tables

• One big table with "ID" and "Rev" as a primary key.
• Active revision as revision with the latest "approved by" date.
• If number of fields exceeds DBMS limit, break up the table into several "one-to-one" fields each with "ID" and "Rev" as the primary key.

Option 2: Entity-value

• A main table (ID, Rev)
• For each data type:
  
  • <data_type>_category table describing each field of that data type
  • <data_type>_value table (ID, Rev, category, value) for each instance of a value

Option ?: Any idea you come up with...

Note: If applicable, I plan to use MS-Access for the entire project (Forms, tables, queries, and reports). We also have SQL Server and an MSDN subscription, so if you strongly suggest, that is an option too.

EDIT:

• This will be an in-house database.
• We have about 150 specifications, and I expect about one new revision per specification per year.
• I will most likely maintain this database as issues arise.
• Approximately five people should be able to create and update specs and revisions.
• Approximately twenty-five employees will retreive data from this database (print reports, lookup specs, etc.)

EDIT2: (Clarifications for HansUp)

• I will reconsider using Lookup Fields and consider other options (creating another table, text values, etc)
• By "Each field will have a particular data-type," I meant that each field (or "blank") will have a corresponding data-type. For example, MAX_CHAMBER_PSI will always be an integer value, PERFORM_TEMPERATURE_TEST will always be a Yes/No, and REVISION_REASON will always be a string.
• The number of users is just an approximation. About five users will have create/update access, and about twenty-five additional users will only be able to read the data. On form_load, I just planned to hide controls and update properties based on the user logged in.

EDIT3: Summary:

We designed a spec report with ~100 fields all dependent on SpecID and Rev to define the acceptable range for various attributes of our products.

Right now, the specifications are written by hand on the report and stored in a filing cabinet. Also, a copy of the latest revision of each spec is provided in a binder at each test stand.

How should I model a primary key with about a hundred attributes, which exceeds the limit of for columns in a Microsoft Access table?

Answer 1

My advice would be do go with your option #1. #2 is going to be an order of magnitude more complicated, and it's probably unnecessary for your situation. #2 might be justified if you let each user define his own fields, but I don't get that from your description.

Your specification is simply an entity with 100 fields. Modeling it as a "container" of 100 "field objects" is way overkill.

Answer 2

I would recommend the entity value approach, based on my experience trying to do this kind of thing the "simple" way (it's easy to design a flat data structure, but not nearly as easy to work with the data).

Have a look at Duane Hookum's At Your Survey database template. I've not used it myself, but I've seen it discussed at length, and it seems he uses that approach.