Parent Tables and Referential Integrity

  • Thread starter Thread starter Rob
  • Start date Start date
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..
 
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/
 
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.
 
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
 
Back
Top