Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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 40

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

572 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