4

I'm sorry I'm new when it comes to databases, I'm using Access right now to develop a database to store calibration data for a bunch of different types of transducers. Each type of item is fundamentally different from the other so I'm planning to make a table for each item type, but across the board they all must have a unique ID number (so it can be related to one or many calibrations). Here is an example in rusty SQL, hopefully it makes sense.

create table accelerometer (GAC_id, name, range, serial)
create table loadcell (GAC_id, name, description)
create table potentiometer (GAC_id, product_id, tag_id)

As you can tell the 3 tables have different columns but across the 3 tables there MUST be a UNIQUE GAC_id number, something that no matter what table there is only one GAC_id in the entire database.

One idea is if I were to create a 'master table' that had a GAC_id and a part type then would have a relationship to the appropriate sub-category part id but I don't think you can create a 'variable' relationship, can you? Following the example above...

create table Part (GAC_ID, part_type, accelerometer_ID/loadcell_ID/potentiometer_ID)

... where it chooses the third column based on the value in the second column

Anyway really need help here, thanks

1
  • what are accelerometer_ID/loadcell_ID/potentiometer_ID. are they artificial keys?
    – miracle173
    Jul 11 '15 at 5:26
1

Your best bet is likely going to be to create a single child table that references all three of the other tables onto a single GAC_id. So you'd end up with something more like this:

create table gac (
    GAC_id,
    accelerometer_id,
    loadcell_id,
    potentiometer_id,
    primary key (GAC_id),
    foreign key (accelerometer_id) refrences (accelerometer.accelerometer_id)
    foreign key (loadcell_id) references (loadcell.loadcell_id)
    foreign key (potentiometer_id) references (potentiometer.potentiometer_id)
)

Here, each foreign key should reference the primary from the appropriate table:

create table accelerometer (
    accelerometer_id,
    name,
    range,
    serial,
    primary key (accelerometer_id))
create table loadcell (
    loadcell_id,
    name,
    description,
    primary key (loadcell_id))
create table potentiometer (
    potentiometer_id,
    product_id,
    tag_id,
    primary key (potentiometer_id))

This will work from a relational standpoint if I understand what you are looking for correctly. Then you just make sure that all items in gac have a unique index associated with them, they cannot be null, and that each entry has to have a corresponding entry in the appropriate parent table. For Access specific SQL, use the MSDN Access SQL Reference to see Access specific queries to perform each task.

From a design standpoint, it would be a horrible idea to have three different tables with identical primary keys (GAC_ids in this case). Theoretically you could do it, but there are all sorts of issues that it can and will cause from a practical standpoint.

Theoretically you could also do something like what you had originally:

create table accelerometer (GAC_id, name, range, serial, primary key(GAC_id, <insert one of the other fields here>))
create table loadcell (GAC_id, name, description, primary key(GAC_id, <insert one of the other fields here>))
create table potentiometer (GAC_id, product_id, tag_id, primary key(GAC_id, <insert one of the other fields here>))

Then you could just create a view that joins them all on the GAC_id. Of course, this will only work if the GAC_ids correlate one-to-one:

create view my_view as select * from accelerometer a
    full outter join loadcell lc ON a.GAC_id = lc.GAC_id
    full outter join potentiometer p ON a.GAC_id = p.GAC_id

That would create a view that accomplishes the same task as the child table in the first explanation i gave, but it has the advantage of auto updating whenever an item is added to any table. the only catch is that if there is no corresponding entry in any of the other tables, you will see null values where those entries should be.

look into the MSDN Access SQL Reference for the specifics on how to do the different things i've mentioned, in Access SQL.

One thing that is worth mentioning is that Access SQL may not support full outter joins. I know that is a quirk with MySQL, but i am not familiar with Access SQL, so you will have to use the reference, and probably this website(dba.stackexchange.com) and google to see exactly how you can/should build your create queries in Access SQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.