Solved

Recommendations for good database design of MS Access Database

Posted on 2016-09-28
6
32 Views
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

John
0
Comment
Question by:boltweb
  • 3
  • 2
6 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41820166
Please send image of your relationships diagram
thanks
0
 

Author Comment

by:boltweb
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
0
 

Author Comment

by:boltweb
ID: 41820214
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 7

Assisted Solution

by:COACHMAN99
COACHMAN99 earned 50 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
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 450 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.
0
 

Author Closing Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now