Parent Tables and Referential Integrity

R

Rob

Hi,

I have a series of table containing data over the past 2 years I am
analyzing with the table analyzer and I am dividing it up and I have
one question. We have a column called 'Issues' and that has a series
of specific issues that are selected from a drop down (or were - all
of this data is being migrated from excel - about 3500 records). The
thing is, the options in that drop down have changed over the past 2
years. The same options that were there 2 years ago are no longer an
option to select as an issue - in fact there are over 300 different types of
issues but we currently only choose from a list of 10. So, if I create a
relationship and
split this into two different tables, and I enforce referential
integrity, it would delete any fields that do not have a reference to
the parent table - my 'issues' table.


Is that correct? What would you recommend if you were dividing up
this table? Maybe I should just not enfore referential integrity when I am
defining the relationships and just allow the drop down menu for current
records to only select specific values from the parent Issues table. I just
want to make sure I am doing this right..
 
J

Jeff Boyce

Rob

Why? As in why are you "dividing up this table?" That is, what will having
more than one table allow you to do?

If you were using a spreadsheet, having a different spreadsheet for
different (stores, years, products, <fill-in your reason>) would probably be
how you'd handle this.

Access is a relational database, though, and not only do you rarely need to
"divide up a table", it's often not a very good idea.

More info, please!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
R

Rob

Currently these issues are tracked by about 5 different people. Each record
consists of 10 different fields. These fields have changed and most have
been added over the last year or so. I want to have it so that each of these
5 people have a 'front end' where they can enter a ticket in. I want only
one or two of those people to have the ability to change issues (I figured I
would use a lookup column and have the form change the table the lookup
column references), and I could easily track everything with different tables
according to several of these fields in my main table.

For example, I will have a manager field, an account field, a resolved by
field, etc. I want to have a resolved by table, a manager table, etc so that
I can easily see which tickets fall under each.

From what I understand, access would be perfect for this. It is either this
or the IT dept. will step in and start using this service-now product, which
I don't like.

Any suggestsions Jeff? Thanks in advance.
 
J

Jeff Boyce

If the "fields have changed", your table probably works more like a
spreadsheet than a relational database (not a good thing).

A relational database (like Access), gives you both a way to efficiently
store data, and a very capable searching tool (queries).

Without understanding how your data is currently structured, it will be
tough to suggest how to query it.

But using a separate table for tickets handled one way vs other ways is a
formula for a maintenance nightmare!

Instead, a single table that has the ticket plus the handling method gives
you a easy way to query by handling method (e.g., show me all the tickets
handled by "manager").

Regards

Jeff Boyce
Microsoft Office/Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top