one to one relationship help and database design question

[Guest]

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?

 

[Jamie Collins]

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.

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.

See this example of subclassing, which uses a Vehicle superclass and
subclasses of SUV, Sedan, etc:

http://groups-beta.google.com/group/comp.databases.theory/msg/2f2e1ff43b17682c

"use a compound candidate key (vin, vehicle_type) and a constraint in
each sub-class table to
assure that the vehicle_type is locked and agrees with the Vehicles
table."

Jamie.

--

 

[Guest]

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?

 

[Jamie Collins]

i think it is sql that is shown
there.

Sure is Polite hint: you can't get far in DBMS-land without
knowledge of the SQL language. A quick/simple tutorial may be found
here:

http://sqlcourse.com/intro.html

CREATE TABLE is introduced in lesson 4.

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?

Allowing for typos and ellipses, the SQL DDL can be used in Access/Jet
while in ANSI-92 query mode e.g. using an ADO connection. The CHECK
constraints may be implemented as (field-level) Validation Rules. The
following VBA code creates a new database (C:\DropMe.mdb) to contain
the tables with CHECKs and Validation Rules:

Examine the structure, noting that you can only create vehicles of type
SED or SUV, that a SUV cannot be inserted into Sedan and likewise a SED
cannot be inserted into SUV:

Sub JCelkoVehicles()

' Create database
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"

' Create tables
With .ActiveConnection
.Execute _
"CREATE TABLE Vehicles ( vin CHAR(17) NOT" & _
" NULL PRIMARY KEY, vehicle_type CHAR(3)" & _
" NOT NULL, CHECK(vehicle_type IN ('SUV'," & _
" 'SED')), UNIQUE (vin, vehicle_type) );"

.Execute _
"CREATE TABLE SUV ( vin CHAR(17) NOT NULL" & _
" PRIMARY KEY, vehicle_type CHAR(3) DEFAULT" & _
" 'SUV' NOT NULL, CHECK(vehicle_type = 'SUV')," & _
" UNIQUE (vin, vehicle_type), FOREIGN KEY" & _
" (vin, vehicle_type) REFERENCES Vehicles" & _
" (vin, vehicle_type) ON DELETE CASCADE ON" & _
" UPDATE CASCADE ); "

.Execute _
"CREATE TABLE Sedans ( vin CHAR(17) NOT NULL" & _
" PRIMARY KEY, vehicle_type CHAR(3) DEFAULT" & _
" 'SED' NOT NULL, CHECK(vehicle_type = 'SED')," & _
" UNIQUE (vin, vehicle_type), FOREIGN KEY" & _
" (vin, vehicle_type) REFERENCES Vehicles" & _
" (vin, vehicle_type) ON DELETE CASCADE ON" & _
" UPDATE CASCADE ); "

End With

' Create Validation Rules
Dim jeng
Set jeng = CreateObject("JRO.JetEngine")
jeng.RefreshCache .ActiveConnection

.Tables("Vehicles").Columns("vehicle_type") _
.Properties("Jet OLEDB:Column Validation Rule").Value = _
"IN ('SUV', 'SED')"

.Tables("SUV").Columns("vehicle_type") _
.Properties("Jet OLEDB:Column Validation Rule").Value = _
"='SUV'"

.Tables("Sedans").Columns("vehicle_type") _
.Properties("Jet OLEDB:Column Validation Rule").Value = _
"='SED'"

Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[Guest]

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.

 

[Jamie Collins]

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.

--

 

[Guest]

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.

 

[Guest]

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.

 

[Jamie Collins]

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?

Think 'data integrity' (redundancy is different consideration).
Implement the steps and you should have effective constraints that
prevent, say, a compressor being inserted into the dispensers table.

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

I don't think like that! Horses for courses...

how will this affect things like <<help! what do such things mean?>>
{i will assume now that you dont know, because you aren't and access
UI expert.}

....you are correct <g>! I suggest you repost any specific questions in
one of the microsoft.public.forms groups, were some real experts are
lurking.

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

That wasn't my intention You were supposed to merely slap your
forehead, say something like 'Doh!' or 'S**t, you're right!' and
schedule in a fix for the flaw.

Jamie.

--

 

[Jamie Collins]

note the left-to-right column order, MachineTypeID then MachineID,
[in the PRIMARY KEY declaration] is significant;

left to right and significant, why?

Briefly, physical ordering on disk ('clustered index').

In Access/Jet, the 'primary' index (the PRIMARY KEY constraint or in
its absence the chronologically first created UNIQUE constraint)
determines the physical ordering when the file is compacted (subsequent
rows are inserted in date/time order).

Think paper copy telephone directory. It can have only one physical
order (obviously). If it were ordered on telephone number, would this
be useful? No. It is ordered on last_name then first_names e.g. PRIMARY
KEY (last_name, first_names). The left-to-right order is significant
here because it is not the same as (first_names, last_name) first_names
then last_name, being an entirely different and less useful set
ordering. Physical ordering on last_name first will particularly favour
tasks such as, 'Grab me all the people who's last name begins with the
letter 'p'.

I've assumed having your 'main' table physically ordered on machine
type will be beneficial e.g. to GROUP BY queries. There is a counter
argument, and a good one, that physically ordering on a random integer
(e.g. autonumber) column will improve concurrency (although with Jet
4.0's record-level locking this argument is less strong). I assumed you
wouldn't be able to make a judgement on concurrency considerations
because, well, there doesn't seem to be anything relevant in the Access
documentation!

This is a bit of a controversial issue because historically the Access
documentation said to say that Jet does not 'support' (operative word)
clustered indexes
(http://msdn.microsoft.com/archive/d...e/en-us/office97/html/output/F1/D2/S5A274.asp)
whereas the more recent/detailed documentation confirms that a concept
of 'non-maintain clustered indexes' does indeed exist
(http://support.microsoft.com/default.aspx?scid=kb;en-us;137039 and
http://support.microsoft.com/default.aspx?scid=kb;en-us;209769). Excuse
me while I prepare to be flamed once more for daring to speak the
truth... <g>.

HTH,
Jamie.

--

 

[Jamie Collins]

i have a product table, that table
is connected to the "Compressor" table, "Dispenser" table and 3 other tables.
these 5 tables have the ProductID and the ProductTypeID that link them to the
Product table. should only one of these fields be the primary key in the
Product table and the additional five?

have a look at the first picture on this site:

http://www3.telus.net/actonn/

notice that i have a primary key on each of the 5 subtables. these are not
the primary key from the product table. additionally i have a separate field
for the primary key for each subtable. should i just change that to match
your drop me database? would that "tighten" up the data validity?

Forget primary keys for the moment.

The columns ProductID and ProductTypeID should be NOT NULL (Required =
Yes.True).

The candidate keys for this table are:

1) (ProductID)
2) (ProductTypeID, ProductID)

The natural key is 2) and would go as far as saying you need to enforce
it using a UNIQUE constraint.

You could also put a UNIQUE constraint on 1) but this isn't really
necessary if you enforce the natural key and put a Validation Rule on
ProductTypeID to ensure it can only relate to a compressor (or whatever
is the product specific to the table). A UNIQUE constraint on 2)
however would allow you to use the, should you want to reference
compressors in another table and you are 'phobic' about compound
foreign keys.

Every table should have a PRIMARY KEY so you should choose one of your
candidate keys to promote to PK. How do you choose? Well, that's a
whole topic in itself. The important thing to note is that choosing a
PK has nothing to do with theory: relational theory has moved on all
keys are now considered equal but SQL was designed on the basis that
the SQL product implementation would give special treatment to the PK.
This is true of Access/Jet:

"If a primary key exists in the table, compacting re-stores table
records into their Primary Key order. This provides the equivalent of
Non-maintained Clustered Indexes"

(source: MSDN: ACC2000: Defragment and Compact Database to Improve
Performance:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209769)

You won't find much discussion on clustered indexes in Access/Jet but
you will for other SQL products that are based on similar technology,
notably SQL Server. Hints: queries using GROUP BY ProductTypeID would
favour PRIMARY KEY (ProductTypeID, ProductID) whereas concurrency would
benefit from PRIMARY KEY (ProductID) if values are far apart e.g. a
random (not incrementing) autonumber.

Your column CompressorID would appear redundant. I know many in the
Access community like to add an autonumber to every table and even make
it the PRIMARY KEY (being more often than not the wrong choice of PK)
but if the ProductID is an autonumber from another table and is
guaranteed to be unique in this table, what does CompressorID give you
that ProductID does not? Nothing that I can see. [I don't mean to sound
harsh I'm merely asking you to reflect on some choice you have made,
perhaps without too much consideration.]

In ('SUV','SED')

this comes up in the main table in the validation rule of the vehicle type
field. is this equivelant to = "SUV" or "SED" as a validation rule?

You are correct. The Validation Rule

IN ('SUV', 'SED')

can be expanded as

(vehicle_type = 'SUV' OR vehicle_type = 'SED')

should i have on the Product table in the productTypeID a
validation rule that says IN ('Compressor', 'Dispenser', etc.)

Yes, unless you have a table of 'product types' in which case there
would be a foreign key ('Relationship') between the tables 'products'
and 'product types', using the referencing column 'productTypeID'
presumably.

Jamie.

--