If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multivalue Fields and Normalization
In general are multivalue fields a bad idea to use? I am trying to ensure my
small database is normalized as possible, but it seems that I every table I try to create ends us breaking into smaller related tables and these having to be additionally broken. It seems that multivalue fields will allow me to eliminate some of this, but I do not want to lose functionality for conveniance. Thanks in advance. |
#2
|
|||
|
|||
Multivalue Fields and Normalization
On Tue, 3 Feb 2009 19:47:00 -0800, Brian Carlson
wrote: In general are multivalue fields a bad idea to use? I am trying to ensure my small database is normalized as possible, but it seems that I every table I try to create ends us breaking into smaller related tables and these having to be additionally broken. It seems that multivalue fields will allow me to eliminate some of this, but I do not want to lose functionality for conveniance. Thanks in advance. I'd avoid them. They are available in Access, but NOT in any of the databases to which you might transfer it - SQL/Server, Oracle, MySQL, etc. They're very much nonstandard. Under the covers, they're actually implemented using a related table ANYWAY - a concealed related table that you can't at all easily work with. Using the Table Analyzer may be causing you to "overnormalize" - it gives suggestions, sometimes good ones, but it has no human intelligence. Database normalization is an art as well as a science, and requires some judgement; and AFAIK no computer yet has that attribute. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Multivalue Fields and Normalization
Multi-valued fields (MVF) do not avoid those small tables you refer to; it
merely hides them. Hidden away in your database, you still have a related table: it's just that you *can't* get to that table. That's one of the main problems with MVFs: you're limited in what you can do with them. If you are a complete novice who has no idea what normalization is, they may help you get where you need to go. But if you know what a related table is, you're better of creating one IMHO, rather than snooker yourself with stuff you can't get at. There are other issues as well, e.g.: - upsizing: 'real' databases don't have MVFs. :-) - limitations in SQL statements (e.g. ... FROM Table1 IN 'c:\somefile.accdb') - problems with filters: http://support.microsoft.com/kb/926701 - problems with OldValue (not maintained correctly.) And, of course, you have to re-write any generic code you use for handling recordsets, because a 'field' is no longer a discrete value. (Some would that storing complex (non-atomic) data in a field is itself a violation of normalization.) So, if you know enough to ask the question, my recommendation would be not to use them. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Brian Carlson" wrote in message ... In general are multivalue fields a bad idea to use? I am trying to ensure my small database is normalized as possible, but it seems that I every table I try to create ends us breaking into smaller related tables and these having to be additionally broken. It seems that multivalue fields will allow me to eliminate some of this, but I do not want to lose functionality for conveniance. Thanks in advance. |
#4
|
|||
|
|||
Multivalue Fields and Normalization
While the other posters' points are very true, I do think multi-valued fields
are great for small databases that are never destined to be anything larger than Access. If there's any chance that you're going to have to transition this database to another system, save yourself the trouble and avoid MVF's. However, if this is something small, maybe for an individual department or office, I've found multi-valued fields to be a nice feature. Mark "Brian Carlson" wrote: In general are multivalue fields a bad idea to use? I am trying to ensure my small database is normalized as possible, but it seems that I every table I try to create ends us breaking into smaller related tables and these having to be additionally broken. It seems that multivalue fields will allow me to eliminate some of this, but I do not want to lose functionality for conveniance. Thanks in advance. |
#5
|
|||
|
|||
Multivalue Fields and Normalization
Define "nice" ... ?easy to set up ... ?easy to make do what you want
Regards Jeff Boyce Microsoft Office/Access MVP "Mark" wrote in message ... While the other posters' points are very true, I do think multi-valued fields are great for small databases that are never destined to be anything larger than Access. If there's any chance that you're going to have to transition this database to another system, save yourself the trouble and avoid MVF's. However, if this is something small, maybe for an individual department or office, I've found multi-valued fields to be a nice feature. Mark "Brian Carlson" wrote: In general are multivalue fields a bad idea to use? I am trying to ensure my small database is normalized as possible, but it seems that I every table I try to create ends us breaking into smaller related tables and these having to be additionally broken. It seems that multivalue fields will allow me to eliminate some of this, but I do not want to lose functionality for conveniance. Thanks in advance. |
#6
|
|||
|
|||
Multivalue Fields and Normalization
Sure, by "nice" I mean they are both easy to set up and easy to use. Most
databases are designed with other end users in mind besides the database creator. In most cases, these end users aren't as knowledgeable about proper database design and use, so it is important to design the database and its interface in as simplistic a fashion as possible, both so that it's easy for them to use and so that they don't "screw it up" by entering unnormalized data. Rather than making users create entries themselves in a separate related table, MVF's simply allow them to choose the applicable values from a list of choices. It is more of an intuitive process than the alternative. Mark "Jeff Boyce" wrote: Define "nice" ... ?easy to set up ... ?easy to make do what you want Regards Jeff Boyce Microsoft Office/Access MVP "Mark" wrote in message ... While the other posters' points are very true, I do think multi-valued fields are great for small databases that are never destined to be anything larger than Access. If there's any chance that you're going to have to transition this database to another system, save yourself the trouble and avoid MVF's. However, if this is something small, maybe for an individual department or office, I've found multi-valued fields to be a nice feature. Mark "Brian Carlson" wrote: In general are multivalue fields a bad idea to use? I am trying to ensure my small database is normalized as possible, but it seems that I every table I try to create ends us breaking into smaller related tables and these having to be additionally broken. It seems that multivalue fields will allow me to eliminate some of this, but I do not want to lose functionality for conveniance. Thanks in advance. |
#7
|
|||
|
|||
Multivalue Fields and Normalization
On Wed, 4 Feb 2009 12:08:04 -0800, Mark
wrote: Rather than making users create entries themselves in a separate related table, MVF's simply allow them to choose the applicable values from a list of choices. So do properly designed Forms with Subforms. Users should certainly NEVER be required to "create entries themselves" - in fact should never even see a table datasheet! -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Multivalue Fields and Normalization
John:
You have touched on the crux of my problem. I am using a main form with multiple subforms. One of these subforms has a subform underneath it. However, the first tier subform will not allow continous view, which is causing me problems. "John W. Vinson" wrote: On Wed, 4 Feb 2009 12:08:04 -0800, Mark wrote: Rather than making users create entries themselves in a separate related table, MVF's simply allow them to choose the applicable values from a list of choices. So do properly designed Forms with Subforms. Users should certainly NEVER be required to "create entries themselves" - in fact should never even see a table datasheet! -- John W. Vinson [MVP] |
#9
|
|||
|
|||
Multivalue Fields and Normalization
So you are investigating alternatives to sub-subforms, Brian?
In the old Northwind sample database, there's a form named Customer Orders. It has a subform where you select an order. When you do, the details of that order appear in another subform below it. This is not a sub-subform (i.e. both subform sit directly on the main form.) Its Link Master Fields is: [Customer Orders Subform1].Form![OrderID] I've used a modified version of that approach several times, so when you click on a record in one continuous subform, the details are displayed in the other continuous subform beside it. If the sub-subform is for display purposes only, another option might be to place a list box in the Form Footer of your continuous subform. For me, those are preferable to MVFs. For you, take your pick. :-) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Brian Carlson" wrote in message ... You have touched on the crux of my problem. I am using a main form with multiple subforms. One of these subforms has a subform underneath it. However, the first tier subform will not allow continous view, which is causing me problems. |
#10
|
|||
|
|||
Multivalue Fields and Normalization
On Wed, 4 Feb 2009 16:40:00 -0800, Brian Carlson
wrote: John: You have touched on the crux of my problem. I am using a main form with multiple subforms. One of these subforms has a subform underneath it. However, the first tier subform will not allow continous view, which is causing me problems. You can use "correlated subforms" with a little bit of hassle. For example you can put a textbox on Mainform with a control source =SubformA.Form!LinkingFieldControl and use that textbox as the MasterLink field for SubformB, to make SubformB dependent on the currently selected record on SubformA. -- John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|