Spec sheet database design

[giovle]

Dear,




For a project I would need to design a database containing spec sheets for different materials. This database should be easily consulted for non-Access users. I have a number of different materials that have different properties. Most properties are easy to put into fields, however, several properties are organized in a category. As an example, a material can have different properties for storage (this would be a field for storageProperties, having multiple values). Other materials can share some of these properties or can have other properties (as I imagine, this is a many to many relationship).

In the end it should be possible to create an easy to access database, where you can go to a certain record (a certain material) and get an overview of all the properties. The simple properties would be shown as a "label - value" and the categoric properties would be shown in a "label - listbox" kind of way.

As I imagine I would have the following tables:

• tblMaterial: table containing a record for each material, with materialID as a primary key, and each of the 'simple' properties contained in their own field
• tblCategoricProperty: a separate table for each set of categoric properties, with propertyID as a primary key, and a record for each value the property can have
• jnctTblCategoricProperty: a junction table for each tblCategoricProperty, with materialID and propertyID both as primary keys, and a record for each materialID-propertyID combination

First question with this: is this the most suitable way to go? (I hope my explanation made some sense ...)

If so, how would I best start out to create a form that displays the full spec sheet for a certain material? So one spec sheet for each materialID and all related properties connected to it.

Would it be possible to create 1 form to input new data or manipulate records of a certain material? The difficulties I think of atm are that it would need to be possible to create new values for a categoric propertie on the fly if a new material has totally different properties (the categories would always stay the same, but a new material can have a combination of values for a certain categoric property that are already present in the database, as well as new values for that same categoric property). Can someone give me a lead on how to go about this?

I really hope my explanation made sense, as it is already kind of foggy in my mind.


Thanks already

[Micron]

Some of your statements can be interpreted in more than one way. Here's my take on them:
(this would be a field for storageProperties, having multiple values) if you mean a multi-value field, then definitely not. Best to avoid those.
each of the 'simple' properties contained in their own field No. If you EVER have to add a field to add an attribute for anything your db is not normalized.
a separate table for each set of categoric properties...and a record for each materialID-propertyID Again, no. Multiple tables for similar attributes or entities is another sign of poor normalization. You appear to be thinking of mixing categories and properties in the same table, the first being definitive, the second being an attribute of either a material or a category.
how would I best start out to create a form... You "never" would start with forms. In a nutshell,
- you should start with a well defined statement as to what you want the database to be able to do. If you cannot write that down, don't expect anyone else to understand the requirements.
- consider what data would need to be entered, what should be editable, retrievable, what should not.
- ensure your design is properly normalized and all tables have the necessary fields with appropriate data types and that related fields have identical data types.
- with test data, you play with queries and try to edit/add records. If you cannot, the query is not updatable and this might cause a problem or it could be acceptable.
- The thing about Access is that it is a tool that is relatively easy to use, but not so easy to use well. DO read up on normalization, reserved words, naming conventions (to point out a few) and save yourself some head banging later. Here are some things you should review:
http://www.fmsinc.com/free/newtips/primarykey.asp (note left nav pane as well, with lots of supporting topics)
http://www.access-programmers.co.uk/...d.php?t=225837
http://access.mvps.org/access/general/gen0025.htm
http://www.utteraccess.com/wiki/index.php/Autonumbers
http://access.mvps.org/access/general/gen0012.htm
http://allenbrowne.com/AppIssueBadWord.html
https://www.techonthenet.com/access/functions/

You really haven't provided enough info about the business case (as mentioned) to advise on table structure. For example, is Color a category or a property? Maybe "metals" is a category level 1 and steel is a category level 2? Maybe steel is a category and formability, tensile strength, hardness are properties? No one knows.

[giovle]

Thank you for replying so extensively. I will try to be a bit more specific/concrete in my explanation. All materials in the material library I'm trying to set up, have the following properties:

• Trade name of the material (short text)
• Material family (short text)
• Description (long text)
• Supplier (short text based on a list of suppliers which can be updated)
• Status (short text based on a fixed list)
• Issues (a list of issues, which are essentially sentences describing the issue) --> some issues are valid for several materials, some are not (the list with issues can be updated) e.g.
  
  • Material is hard to process
  • Material is expensive
  • Material needs to be pretreated before use
  • ...
  
  
• Storage recommendations (a list of recommendations regarding storage, which are essentially sentences describing the recommendation) --> analogous to the issues (the list with recommendations can be updated)
  
  • Needs to be stored dry
  • Needs to be stored in the dark
  • Needs to be stored in a ventilated cupboard
  • Needs to be stored in a locked cabinet

There are other properties like the storage recommendations but that would make the list above too long.

I don't want a multiselect field, I'd rather use a junction table to accomplish this feat (e.g. a table containing a storageRecommendationID and storageRecommendationDescr + a junction table with a materialID and a storageRecommendationID for all the combination of these two).

each of the 'simple' properties contained in their own field No. If you EVER have to add a field to add an attribute for anything your db is not normalized.

I don't entirely get your point here. With a simple property I mean for example the 'material family'. This would be a field, present in the materials table, that contains a short text value.

I'll stick to these two issues for now, depending on your response (just to break my issue down into bits).

Again, thanks for taking the time to reply.

[orange]

How about taking a few examples of your real data and showing readers a "typical" entry in the materials library? Just trying to clarify communications.

I agree with Microns comments --well done.

[giovle]

I have attached three files. Two files contain fictive material data sheets as I want the end user of the library to see. This contains information on 1 material and lists all corresponding properties. The entries that are available for selection for the fields 'Supplier', 'Status', 'Issues' and 'Storage' can be found in the third file.

lists.pdfmatA.pdfmatB.pdf

As you can see, the fields for 'Issues' and 'Storage' can contain several values.

Does this clarify the set up of my problem?

[Micron]

With respect to your quoted comment: meaning if you identify a new attribute (e.g. red) of an entity/characteristic/property (e.g. color) and you have to add a table field to accommodate those that are red, the design is likely not normalized. However, if you start out with tblCust.Phone and later decide you want to add a secondary phone number, this would be insufficient planning - as per my first bullet point. Without the benefit of seeing sample data or speaking the same level of Access terms/jargon, "each of the values contained in their own field" could mean the scenario I'm describing. It could also mean you want to store all of the values in one field. If you don't know what a mvf is, check it out. Likely you are not meaning this (which BTW is not a "multi select field" as you might have referred to).

For me, the answer falls back to my bullet points about design since your samples don't provide enough information to give a definitive answer. These are not representations of your data - they are results from it, but it is a start. However I think I get your drift and rather than belabor the issue, I'll say you need tables for issues and storage requirements and junction tables for materials/storage and materials/issues since your preference seems to be for junction tables. 3 storage requirements means 3 entries for Material A's ID. Similarly, 2 issues for Material A means 2 entries for Material A. The alternative is one junction with Material A listed 5 times because it has 3 of one and 2 of the other, but for any given row, the non-applicable fields are empty so your table is full of holes.

Once you reach the table design phase, you could post an image of your relationships window, but at the risk of repeating myself, if we don't understand the requirements, we're somewhat limited in our ability to advise. You have to assume we know NOTHING about your case, because we don't

[orange]

Is your material the same as Product?


I have seen a recent post where there were a number of items and these items had very diverse attributes. One suggestion was to use an EAV (entity attribute value ) design.

You may find this an interesting article as well.

I'm not trying to be confusing -- just showing some thoughts that may be relevant to your situation.

Good luck.