one to one relationship help and database design question

[DawnTreader]

hello

i have a database where i am trying to store information about 5 different
types of machines by using a table that holds the common information, with
relationships to tables with the uncommon information in a one to one
relationship.

there is an image of the relationship structure here:

http://img73.imageshack.us/my.php?image=relationshipsnov6mx2.jpg

the area in question is the machinelist table and the subdata tables. each
subdata table is a different type of machine. the problem with this
arrangement is this; each machine can have one record in each of the five
tables! that means one machine can be all five types. this is not what i had
intended with this structure. i was hoping that there was a way to cause the
MachineID to only be used once with one of the five types of machines.
meaning that one record in the machinelist table, has only one corresponding
record in one of the five subdata tables.

how can i do this?

 

[DawnTreader]

Hello

hmmm. that is just a little over my head. i think it is sql that is shown
there. but how do i implement that in an access database? where do i put the
"code" or what do i change to create this set of checks?

thanks for the pointer, can you elaborate?

 

[DawnTreader]

Hello

ok.

i know you are trying to help, but this is way beyond me at this point.
there is no way i am ready to start with all these fancy initials. i took a
look at the relationship window in access. thats cool. but if i cant make it
without learning a lot of programming, i am hooped.

i am currently working on a database that is of urgent need. learning the
indepth stuff you are telling me wont help me, right now. now i am greatful
for your interest in helping me, but how am i going to implement that in what
i have currently?

have a look at this:

http://img73.imageshack.us/img73/8365/relationshipsnov6mx2.jpg

i need the machineID to show up only once in the whole database. machine #
42 can only be a compressor, and so that machine ID cannot be allowed to show
in the dispenser table. as my database currently stands there is no way,
except through interface, to ensure this.

i understand that your method appearantly causes the situation i want, but
how do i create that situation in my current database? i cannot create a
whole new database, i have hours of data input already done to this and would
like to keep everything intact and i have limited time constraints on getting
this implemented. will what you propose cause me to have to do a lot of
additional coding to implement it in the interface? what happens when i
create a form based on one of the tables?

i am sorry if i sound frustrated, my boss would like to get this done so we
can use it to do the business that we are supposed to be doing. he doesnt
always understand the fact that it takes time to develope this stuff.

 

[DawnTreader]

Hello again Jamie

thanks for coming back again.

this actually makes a little sense to me when i first scan it.

what i have done is made one table that stores all the characteristics that
are the same for all our products. we are trying to firm up the data
structure and build an interface to use this database in actual day to day
situations.

we build natural gas compressors, dispensers and other equipment for use at
a customers filling site. at each site there is a possiblity of having more
than one compressor, dispenser and other equipment. the thing is that they
all have some data in common. hence the reasoning for tables that store the
data that will be similar and seperate tables for the types of machines.

the machine type problem you mention i had never realised until you said it.
i feel so childish and stupid now...

so if i follow the steps you mention i will remove redundancy, and i will
end up with a set of relationship like your code database?

how will this affect things like lookup combo boxes. not ones created by the
lookup in the tables design, but ones like the forms combo box creation
wizard? {i will assume now that you dont know, because you aren't and access
UI expert.}

i am going to go through this procedure and see what happens in a duplicate
test database. if i have further questions i will get back here with them
soon.

thanks for your help, and your willingness to 'stoop' to my amatuer level of
knowledge of access.

i know you are trying to help, but this is way beyond me at this point.
there is no way i am ready to start with all these fancy initials. i took a
look at the relationship window in access. thats cool. but if i cant make it
without learning a lot of programming, i am hooped.

i am currently working on a database that is of urgent need. learning the
indepth stuff you are telling me wont help me, right now. now i am greatful
for your interest in helping me, but how am i going to implement that in what
i have currently?

have a look at this:

http://img73.imageshack.us/img73/8365/relationshipsnov6mx2.jpg

i need the machineID to show up only once in the whole database. machine #
42 can only be a compressor, and so that machine ID cannot be allowed to show
in the dispenser table. as my database currently stands there is no way,
except through interface, to ensure this.

i understand that your method appearantly causes the situation i want, but
how do i create that situation in my current database? i cannot create a
whole new database, i have hours of data input already done to this and would
like to keep everything intact and i have limited time constraints on getting
this implemented. will what you propose cause me to have to do a lot of
additional coding to implement it in the interface? what happens when i
create a form based on one of the tables?

i am sorry if i sound frustrated, my boss would like to get this done so we
can use it to do the business that we are supposed to be doing. he doesnt
always understand the fact that it takes time to develope this stuff.

Sorry, I don't have the information I would need to solve your problem.
I'd need to make some assumptions...

What is MachineType? I'll guess it is the following set of values,
reflecting the names of your five subdatatbl- tables (I hate the
prefixes!): {'Compressor', 'Storage', 'Dispenser', 'DecantingPost',
'FillPost'}. However, such a unique set of types would make your
MachineTypeID redundant - TypeID is an oxymoron! - and using the type
(text) in your other tables would increase readability of your data. So
I assume MachineTypeID is an 'uniquifier' autonumber to fake an exposed
surrogate, another design choice which I do not advocate but I'll go
with it, just for you...

In a nutshell, I think you need to complete these steps (or similar):

1) To each of your five subdatatbl- tables add a MachineTypeID column
of to reflect the column MachineID on table tblMachineList: I'll guess
INTEGER (Number, Long Integer) NOT NULL (Required = True/Yes). Don't
make it an autonumber, though.

2) To each of your five subdatatbl- tables add a validation rule to
ensure the MachineTypeID corresponds to that table; assuming
{MachineTypeID=42, MachineType='Compressor'}, the Validation Rule on
table subdatatblCompressor column MachineTypeID would be

=42

[Do you see how

='Compressor'

would make this Validation Rule more dolphin-friendly?]

3) UPDATE each of your five subdatatbl- tables to populate the new
MachineTypeID column e.g.

UPDATE subdatatblCompressor
SET MachineTypeID = 42;

How you configure the Query Builder UI thing to write that SQL for you
is a mystery to me <g>.

4) In your main tblMachineList table replace the single-column PRIMARY
KEY (MachineID) with a composite PRIMARY KEY (MachineTypeID, MachineID)
[note the left-to-right column order, MachineTypeID then MachineID, is
significant; I know how to specify this in code but not using the UI -
but then I'm not Access UI expert!]

5) Change the FOREIGN KEY ('Relationship') from this:

(MachineID) REFERENCES tblMachineList (MachineID)

to this

(MachineTypeID, MachineID) REFERENCES tblMachineList (MachineTypeID,
MachineID)

IIRC this involves dragging the referencing column MachineTypeID (i.e.
the one in the subdatatbl- table) and dropping it on the referenced
column (i.e. the one in the table).

6) In each of your five subdatatbl- tables replace your single-column
UNIQUE (MachineID) with constraint ('Index') with UNIQUE
(MachineTypeID, MachineID). I'm not entire sure but maybe if you
specify a one-to-one when creating the 'Relationship' these constraints
get created for you (and is that is supposed to be a good thing?!)

Do you get the impression I don't know my way around the UI <g>?!

HTH,
Jamie.

 

[DawnTreader]

Hello again

[note the left-to-right column order, MachineTypeID then MachineID, is
significant; I know how to specify this in code but not using the UI - but
then I'm not Access UI expert!]

left to right and significant, why?

i know you are trying to help, but this is way beyond me at this point.
there is no way i am ready to start with all these fancy initials. i took a
look at the relationship window in access. thats cool. but if i cant make it
without learning a lot of programming, i am hooped.

i am currently working on a database that is of urgent need. learning the
indepth stuff you are telling me wont help me, right now. now i am greatful
for your interest in helping me, but how am i going to implement that in what
i have currently?

have a look at this:

http://img73.imageshack.us/img73/8365/relationshipsnov6mx2.jpg

i need the machineID to show up only once in the whole database. machine #
42 can only be a compressor, and so that machine ID cannot be allowed to show
in the dispenser table. as my database currently stands there is no way,
except through interface, to ensure this.

i understand that your method appearantly causes the situation i want, but
how do i create that situation in my current database? i cannot create a
whole new database, i have hours of data input already done to this and would
like to keep everything intact and i have limited time constraints on getting
this implemented. will what you propose cause me to have to do a lot of
additional coding to implement it in the interface? what happens when i
create a form based on one of the tables?

i am sorry if i sound frustrated, my boss would like to get this done so we
can use it to do the business that we are supposed to be doing. he doesnt
always understand the fact that it takes time to develope this stuff.

Sorry, I don't have the information I would need to solve your problem.
I'd need to make some assumptions...

What is MachineType? I'll guess it is the following set of values,
reflecting the names of your five subdatatbl- tables (I hate the
prefixes!): {'Compressor', 'Storage', 'Dispenser', 'DecantingPost',
'FillPost'}. However, such a unique set of types would make your
MachineTypeID redundant - TypeID is an oxymoron! - and using the type
(text) in your other tables would increase readability of your data. So
I assume MachineTypeID is an 'uniquifier' autonumber to fake an exposed
surrogate, another design choice which I do not advocate but I'll go
with it, just for you...

In a nutshell, I think you need to complete these steps (or similar):

1) To each of your five subdatatbl- tables add a MachineTypeID column
of to reflect the column MachineID on table tblMachineList: I'll guess
INTEGER (Number, Long Integer) NOT NULL (Required = True/Yes). Don't
make it an autonumber, though.

2) To each of your five subdatatbl- tables add a validation rule to
ensure the MachineTypeID corresponds to that table; assuming
{MachineTypeID=42, MachineType='Compressor'}, the Validation Rule on
table subdatatblCompressor column MachineTypeID would be

=42

[Do you see how

='Compressor'

would make this Validation Rule more dolphin-friendly?]

3) UPDATE each of your five subdatatbl- tables to populate the new
MachineTypeID column e.g.

UPDATE subdatatblCompressor
SET MachineTypeID = 42;

How you configure the Query Builder UI thing to write that SQL for you
is a mystery to me <g>.

4) In your main tblMachineList table replace the single-column PRIMARY
KEY (MachineID) with a composite PRIMARY KEY (MachineTypeID, MachineID)
[note the left-to-right column order, MachineTypeID then MachineID, is
significant; I know how to specify this in code but not using the UI -
but then I'm not Access UI expert!]

5) Change the FOREIGN KEY ('Relationship') from this:

(MachineID) REFERENCES tblMachineList (MachineID)

to this

(MachineTypeID, MachineID) REFERENCES tblMachineList (MachineTypeID,
MachineID)

IIRC this involves dragging the referencing column MachineTypeID (i.e.
the one in the subdatatbl- table) and dropping it on the referenced
column (i.e. the one in the table).

6) In each of your five subdatatbl- tables replace your single-column
UNIQUE (MachineID) with constraint ('Index') with UNIQUE
(MachineTypeID, MachineID). I'm not entire sure but maybe if you
specify a one-to-one when creating the 'Relationship' these constraints
get created for you (and is that is supposed to be a good thing?!)

Do you get the impression I don't know my way around the UI <g>?!

HTH,
Jamie.