Improve company productivity with a Business Account.Sign Up


Recommendations for good database design of MS Access Database

Posted on 2016-09-28
Medium Priority
Last Modified: 2016-09-29
We have an Access database which contains a list of offerings.

Every single offering needs to have these information supplied:

ID number of the offering
Country and City
Name of the offering

Since we collect data for various types of offerings from a couple of affiliate companies, these offerings may have some additional properties, such as descriptions, star rating of the hotel, GPS coordinates etc.

These additional properties may or may not repeat for every single affiliate company.

In the moment, we currently have about 30+ properties collected from different affiliate companies, some of those are in common, some are not.

Currently, what we do is that we create a list of all of the possible available offering additional properties - We assign it an ID number and we give a name to that property, so it's clear enough what that property is about (Table 1).

After that, we link an offering and the additional property in many-to-many table, where we have all of the informations about the property (Table 2):

What's the ID number of the property?
What's the ID number of the offering?
What's the value of the property?
When was it imported?
Who updated it and when?
Was the update manual or automatic?

We currently put all of the values into one "long text" field, regardless if it's the single line field, multi line field, a number or a date. We do it this way, because it's easier for maintenance (if we receive some new property, we add it in the properties list table, and we mark it for import in our import template, so it gets automatically imported from the Excel sheet provided by the affiliate company). This approach to the data is simpler for us, because the properties may vary from one affiliate company to another, and there might be a lot of new properties in the future. By doing it this way, it's a lot easier for us to modify our export procedure, since the script made for export procedure will check only one field.

However, we are limited with this type of database design in a way where we can't display properties in a continuous form with custom height of these properties (for example, we need multi line textbox for large fields like descriptive text, and we need a simple single line field for a property like star rating). We did a way around, by marking some specific properties as multiline in Table 1 (mentioned above), and then those are being displayed in one subform, and the others are displayed in another subform. Otherwise, we are limited in a way where all of the properties have the same height (we don't need multiline field for properties which are obviously not multiline and vice versa, we can't have single line textbox for fields which are obviously multiline fields).

Another problem is that we are mixing a variety of data types in the same field. We basically have text, numbers, dates etc. placed inside this "long text" data type field. Again, this is easier for us because we also have custom export to text procedure, and all of these properties should be considered as text in the end (we would still need to convert numbers to text, once we export the data).

Is this database design the right one, or do you suggest some other type of database design and why do you recommend it to be that way?

Should we simply create separate tables for each affiliate company with their specific additional properties of the offerings? Should we split different data types in a separate columns?

We need to know this because we are going to undertake a large Access database project and we want to make sure the database design is the one that will be most stable, least prone to bugs and issues and is easy to maintain.

Thank you

Question by:boltweb
  • 3
  • 2

Expert Comment

ID: 41820166
Please send image of your relationships diagram

Author Comment

ID: 41820211
Attached is a screenshot showing the relationship on which we need clarification on:

"PlaceholdersImport" is the table that contains a list of properties names, which we later on use as placeholders on export procedure
"Companies" is the table with the list of the affiliate companies
"Links" is the list of offerings
"LinksProperties" is the table that contains the list of the particular offering additional properties

Author Comment

ID: 41820214
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.


Assisted Solution

COACHMAN99 earned 200 total points
ID: 41820237
Unfortunately I have to go into a meeting so will have to leave this for today.
My parting suggestions are:
1. Make a table with all the possible fields (attributes) regardless whether they are all used.
2. If there are 1:M relationships you will need two tables linked on the keys
3. Don't use a long text field for grouping attributes - this will become a headache.

good luck
LVL 41

Accepted Solution

PatHartman earned 1800 total points
ID: 41820388
I'm not sure I understand your description so it would be helpful to see some actual data in the tables.

Generally, it sounds like the wrong approach and I see several things wrong with the schema you posted.  Consistency in naming is critical.  Foreign keys should have the same names as the primary keys to which they link.  Some folks like to suffix them and I don't have any objection to that.  So CompanyID or CompanyID_PK is the primary key and CompanyID or CompanyID_FK is the foreign key.

MultiValue fields are bad juju and I would avoid them like the plague.  Implement the 1-many relationship using a separate 1-side table.  Access implements the MultiValue field correctly under the covers but it hides the implementation from us which causes difficulties in using the MVF in queries and code. The MVF is also not supported by SQL Server or other relational databases so you won't be able to easily upsize if you need to do that.  

Mushing multiple attributes into a single column violates first normal form and when you violate first normal form, you usually live to regret it.

There is a special kind of table you might want to define to handle future unknown attributes.  It is sometimes called "key value pair".  Look it up for a definition.  I used this to good effect in an insurance policy document creation application.  The company was a startup and they were constantly adding new policy types and with each new type came the potential for a new data field that had not previously been defined.  Basically, the table structure is:

RecID (autonumber PK)
PrimaryID (foreign key to the "master" table such as a policy"
FieldID (foreign key to a list of valid attribute names)
FieldValue (always string.  If you need a long text field, create a separate table for those data types or add a second value field in this table and use one or the other.

Having FieldID be a lookup allows you to maintain some control and keep people from carelessly creating new fields due to typos.  It also gives you a place to define the data type of each field.  I also used it to hold a query name that could be used to populate the RowSource of a combo.  In my case, I defined fields and fields for a policy type.  That meant I would get a combo that only showed the valid field names for the specific policy type being entered.  This goes a long way toward reducing data entry errors.

Author Closing Comment

ID: 41821475
Thank you for your assistance. It is much clearer now what we need to do.

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question