Normalization and multivalued fields
I'm having a problem with my students using multivalued fields in access and getting confused about normalization.
Here's what I can make out. If you have a one-to-many relationship, for example
Articles Comments
-------- --------
artID{PK} commID{PK}
text text
artID{FK}
Access allows this information to be stored in what appears to be a single table, something like
Articles
--------
artID{PK}
text
comment
+ value
a "value" that refers to multiple comment values for a comment column, which are actually accessed as a separate table. The specifics of storing values - table, PK and FK - are completely hidden, but you can request a multivalued field, for example. in the example above with the request
INSERT INTO article( [comment].Value )
VALUES ('thank you')
WHERE artID = 1;
But the query doesn't quite reveal the underlying structure of the hidden table that implements the multivalued field.
With that in mind (a disaster in my opinion), my problem is how to help newcomers to database design and normalization understand what their access offers, why it might not be useful, and that this is not a reason to ignore the basics of the relational model. More specific:
- Are there any better ways, besides queries as above, to show the structure behind multivalued fields?
- Are there any good examples where a multivalued field is not good enough and shows the benefit of normalization explicitly?
- Are there easy ways to get the Multi-select rendering output from multiple Access values, but based on separate explicit tables?
Thank!
source to share
I can't give you advice on how to use this feature because I've never used it; however I can give you reasons not to use it.
-
I want to have complete control over what I do. This does not apply to multi-valued fields, so I don't use them.
-
This feature is not expandable. What if you want to add a date field to your comments, for example?
-
Sometimes it is necessary to grow an Access database (backend) to a "large" database (SQL Server, Oracle). These databases do not offer this feature. Often the customer decides which database should be used. I recently had to migrate an Access (frontend) application using the Oracle backend to a SQL-Server because my client decided to ditch their Oracle server. Therefore, it is recommended to limit the use of general functions only.
-
For common tasks like editing lookup tables, I've created generic forms. My existing solutions won't work with multi-valued fields.
-
I have a (homemade) tool that syncs changes to the database structure on my developer site with the database on the clients site. This tool cannot deal with multivalued fields.
-
I have security management tools that can grant SELECT, INSERT, UPDATE, and DELETE rights to tables, or override them. Again, the management tool does not work with multivalued fields.
-
The presence of a separate table for comments allows you to quickly view all comments (by opening the table). You cannot do this with multi-valued fields.
-
You will not see a 1 to n relationship between articles and comments in the database diagram.
-
With a separate table, you can choose whether you want to cascade deleted data in the detail table or not. If you don't, you won't be able to delete the article if there are comments on it. This may be desirable if you want comments to be unintentionally removed.
source to share
It is important to understand the difference between physical and logical relationships. The entire Internet and Web Services (SOAP) today largely implements a data format that is ambiguous.
When you represent multivalued data with a relational database (like Access), then behind the scenes you are using a traditional (and legal) relationship. I cannot stress that as such, the use of multivalued columns in Access is actually a LEGITIMATE relational model.
The fact that the table is not displayed does not negate this problem. In fact, if you represent the invoice (master record and recurring parts) as an XML data cube, then we see two things:
1) you can create and represent this invoice with a relational database like Access 2) such a relational data model that is normalized can ALSO be represented as a SINGLE xml string. 3) deleting an XML record (or row) means that cascading deletion of child rows (invoice details) MUST appear.
So, while it is true that Multi-valued fields were added to Access to work with SharePoint, it is important to know that such data can be mapped to a relational database (if you could not, then Access was unable to consume XML data using tables relational database, since ACCESS REALLY RIGHT NOW).
And on the Internet such as XML and SharePoint, the need to consume, manage, and use such data is not only widespread, but also a major staple of the Internet.
As more and more data becomes complex, we are discovering the need for multi-valued data explosions. Anyone who has used a so-called "quirk" on the Internet in this way relies on and uses data that is actually VERY OFTEN XML and is meaningful (complex) in nature.
As long as a logical (not physical) relational data model is preserved, using multi-value columns to represent such data is possible, and that's exactly what Access does (it maps a relational data model to a complex model). Please note that a complex (xml) data model does NOT have to be relational in nature. However, if you are going to map such data to Access, then the complex multivalued model MUST MATCH THE CONTENT OF THE RELATIONAL model.
This is EXACTLY what's going on in Access.
The fact that such a correct and legitimate mathematical relational model is not revealed is of little importance here. Are we to assume that since Excel does not reveal the binaries used, users will never know about computers? Or maybe we should all be programming in assembly so that we can all learn how computers work properly.
At the end of the day, who cares and why does it matter? The fact that people drive automatic cars today does not give up the idea that they use different mechanisms to drive this car. The idea that we shut down the whole society because someone is going to drive a machine, or in this case, using complex data would be galactic stupidity on our part.
Therefore, keep in mind that SQL Access Extensions exist in Access to query data with multiple values, but it also states that these underlying tables are not displayed. However, as noted above, when exposing such tables, STILL REQUIRE will not be able to modify or mess up the cascading delete, as this function is required TO ENSURE FUNCTION SWITCHING and the CORRECT MATH relational model between a complex data model (xml) and a function of using two to represent such data.
In other words, you can use linked tables to represent a complex data model IF YOU REMOVE the users' ability to play with referential integrity parameters. The RI parameters MUST remain the same as set in these hidden tables, otherwise such data cannot make the trip BACK to XML or the complex data model from which it was consumed.
As noted, for users who are taught how gasoline reacts with oxygen in order to learn how to drive a car or use a word processor and be forced to learn the relational model and expose basic tables, there is little point here.
However, the points made here regarding such open tables are legitimate concerns.
The REAL problem is SQL Server while Oracle etc. cannot consume or represent this complex data. WHILE ACCESS MAY CONSUME such data.
As noted, the sophisticated LONG data ship sailed back! XML, soap, and the mainstream technologies of the Internet are based on this complex data model.
Basically, SQL Server, Oracle and most databases cannot consume this multivalued data representing it without having to create and model such data in a relational way - this is a big disadvantage of SQL Server, etc.
Access stands only in this ability to consume this data.
So, for those who have used a smartphone, iPad, or the web, you are using the basic technologies that are created using the complex data that Access now allows.
The rest of the industry will likely have to follow suit, given that more and more data is inherently complex. If the database industry does not change, then the traditional relational database system will not be a resting place for such data.
The trend of storing data in linked tables is happening at a fast pace right now, and products like SharePoint or even Google Docs are proof of concept. Thus, access only responds to market pressures, and it is likely that other database vendors will have to follow suit or simply abandon the "fad" called the Internet.
XML and complex data structures are STAPLE and the fact of our industry right now is not a problem we all have to run away with, but actually embrace.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
source to share
The technical discussion is interesting. I think the real problem is student understanding. Since it's available in Access, students will use it, and initially it will probably be a simple solution to some design problems. Negatives will come up later when they try to use the data. Maybe a simple example that demonstrates the problems will cause some students to avoid using multivalued fields? Maybe an example of storing data in a different, more convenient format will help?
Good luck!
Peter Bullard
source to share
I know this post is old. But this is not exactly the same as all the other posts I have seen on this topic. This has someone making a good case for using Multi-valued fields ...
As someone trying, who is still trying very hard to find access to Access, I find the discussion of and against using Multi Valued Fields incredibly frustrating.
I'm trying to figure it out, but if everything is so against them, what is the alternative method? It seems that in every search result I find that everyone is telling you how to use multivalued fields and controls, or telling you how awful and what their error is. Many people refer to an alternative to them, but no one says "Here's an example." I am here to find out about these things. And while I know this is an easier concept for a lot of people in these forums, I could actually use some examples to take a look at them.
I am at the point where I have to decide where to go. It would be great to compare examples of using multivalued fields and alternatives and using the control to select multiple values.
Or am I wrong and the combobox functionality where you can select multiple items is only available through Access?
source to share
I want to answer the last of your questions first. There is a way to provide a visual representation of parent relationships with children. It is called subforms. If you get help about subforms in Access this will explain the concept.
I have used subforms in a project where I wanted to display the transaction header in a form and the transaction data in a subform. Nothing gets in the way of this design, even when the data is stored in two normalized tables.
Of course this affects the screen, not the database. It's all. Normalization has to do with storing and retrieving data, not other uses of data.
source to share