One-to-one relationship?

[kristinem]

Hello! While I'm not new to flat databases, I am new to relational databases.
I've been trying to build a database to keep track of my inventory of
scrapbooking supplies which is getting to a point where i can't remember what
i own or what's left of a particular product. I discussed the design of my
database with my coworker (who's specialty is database administration) and he
recommended a one-to-one relationship between the tables in my design.
However, reading some of the posts here, I'm beginning to wonder if that
really is the right way to go about it and I figured I'd ask for some advice.

Here's what I thought of so far:
GeneralProductInfo Table - This will house the basic fields that each record
will have such as ProductName, ProductQuantity, ProductManufacturer, etc.
ProductCategory Table - To hold the different product categories.
InkProducts Table - This will contain the fields specifically for ink
products.
PaperProducts Table - This will contain fields specifically for paper
products
(and so on for the remaining product categories)

All products will use the fields in the GeneralProductInfo table, but will
only occupy a record in one of the catagory tables depending on the kind of
product it is. A product will never need to use more than one of the category
tables.

At first I thought of throwing everything into one database, but it didn't
seem right having all those null fields, and then that's when I came up with
this. However, now I'm wondering if I should have a table for each category
containing both the general product fields and its corresponding category
fields. Does anyone have any suggestions? Is the one-to-one relationship
design too complicated for this purpose? Thanks!

 

[John W. Vinson]

Hello! While I'm not new to flat databases, I am new to relational databases.
I've been trying to build a database to keep track of my inventory of
scrapbooking supplies which is getting to a point where i can't remember what
i own or what's left of a particular product. I discussed the design of my
database with my coworker (who's specialty is database administration) and he
recommended a one-to-one relationship between the tables in my design.
However, reading some of the posts here, I'm beginning to wonder if that
really is the right way to go about it and I figured I'd ask for some advice.

Here's what I thought of so far:
GeneralProductInfo Table - This will house the basic fields that each record
will have such as ProductName, ProductQuantity, ProductManufacturer, etc.
ProductCategory Table - To hold the different product categories.
InkProducts Table - This will contain the fields specifically for ink
products.
PaperProducts Table - This will contain fields specifically for paper
products
(and so on for the remaining product categories)

All products will use the fields in the GeneralProductInfo table, but will
only occupy a record in one of the catagory tables depending on the kind of
product it is. A product will never need to use more than one of the category
tables.

At first I thought of throwing everything into one database, but it didn't
seem right having all those null fields, and then that's when I came up with
this. However, now I'm wondering if I should have a table for each category
containing both the general product fields and its corresponding category
fields. Does anyone have any suggestions? Is the one-to-one relationship
design too complicated for this purpose? Thanks!

This is one of the few cases where one-to-one relationships are in fact
appropriate: "Subclassing". It's complex, but your concern about the null
fields is well taken. I'd go with the GeneralProductInfo table; it should
contain a field for the product category. You'll probably want some VBA code
on the form to switch the SourceObject of a subform to the appropriate
category-specific table (to make it less likely to have a GeneralProduct
record related to *two* categorical tables.

It's complex, and a rather advanced technique, but it would seem to be the
best bet for this problem.

John W. Vinson [MVP]

 

[Armen Stein]

Hello! While I'm not new to flat databases, I am new to relational databases.
I've been trying to build a database to keep track of my inventory of
scrapbooking supplies which is getting to a point where i can't remember what
i own or what's left of a particular product. I discussed the design of my
database with my coworker (who's specialty is database administration) and he
recommended a one-to-one relationship between the tables in my design.
However, reading some of the posts here, I'm beginning to wonder if that
really is the right way to go about it and I figured I'd ask for some advice.

Here's what I thought of so far:
GeneralProductInfo Table - This will house the basic fields that each record
will have such as ProductName, ProductQuantity, ProductManufacturer, etc.
ProductCategory Table - To hold the different product categories.
InkProducts Table - This will contain the fields specifically for ink
products.
PaperProducts Table - This will contain fields specifically for paper
products
(and so on for the remaining product categories)

All products will use the fields in the GeneralProductInfo table, but will
only occupy a record in one of the catagory tables depending on the kind of
product it is. A product will never need to use more than one of the category
tables.

At first I thought of throwing everything into one database, but it didn't
seem right having all those null fields, and then that's when I came up with
this. However, now I'm wondering if I should have a table for each category
containing both the general product fields and its corresponding category
fields. Does anyone have any suggestions? Is the one-to-one relationship
design too complicated for this purpose? Thanks!

This is a good question, and it actually comes up a lot in database
design.

It comes down to the number of fields that will be Null for each type
of Product.

Access tables are limited to 255 fields, so the first thing you should
determine is whether you are anywhere near that limit if you include
all fields in the main Product table.

If you are well within that limit, then it becomes a tradeoff between
the difficulties in handling one-to-one tables, and the inelegance of
all those Null fields. It can be a style choice for the developer.

When there are just a few fields that are specific to certain Types,
we usually use just one table. Then on the forms we enable or disable
those specific fields based on the type.

However, if there are a lot of specific fields, or if there are
related child tables underneath just one type but not the others, then
we use one-to-one tables.

For example, if you have a Person table, and some Persons are also
Employees with their own set of fields and child tables, then the
one-to-one makes more sense.

I hope this helps,


Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Tom Lake]

Hello! While I'm not new to flat databases, I am new to relational
databases.
I've been trying to build a database to keep track of my inventory of
scrapbooking supplies which is getting to a point where i can't remember
what
i own or what's left of a particular product. I discussed the design of my
database with my coworker (who's specialty is database administration) and
he
recommended a one-to-one relationship between the tables in my design.
However, reading some of the posts here, I'm beginning to wonder if that
really is the right way to go about it and I figured I'd ask for some
advice.

Here's what I thought of so far:
GeneralProductInfo Table - This will house the basic fields that each
record
will have such as ProductName, ProductQuantity, ProductManufacturer, etc.
ProductCategory Table - To hold the different product categories.
InkProducts Table - This will contain the fields specifically for ink
products.
PaperProducts Table - This will contain fields specifically for paper
products
(and so on for the remaining product categories)

I'd make one table and include a field ProductCategory which tells if the
record is
for Ink, Paper, etc. unless the vast majority of fields are unique to each
product
category.

Tom Lake

 

[kristinem via AccessMonster.com]

Thank you guys for the advice! After further evaluation, I think for now I
may keep it simple and make a table for each product category seeing as how
the amount of fields per category will hardly ever grow to more than 25 or 30.
Thanks again!

 

[John W. Vinson]

Thank you guys for the advice! After further evaluation, I think for now I
may keep it simple and make a table for each product category seeing as how
the amount of fields per category will hardly ever grow to more than 25 or 30.
Thanks again!

ummmmm...

that was the *complex* approach.

The simple approach is to put all the categories into one table.

If you're going with the subclassing, good, more power to you - but don't do
it because you see it as *simpler*!

John W. Vinson [MVP]

 

[Jamie Collins]

At first I thought of throwing everything into one database, but it didn't
seem right having all those null fields

I think you should trust your intuition here .[For the record, I find
John Vinson's "one-to-one relationships are very rare" position to be
counter intuitive e.g. AFAIK everyone has one and only one birth
mother <g>.] For further inspiration, see:

How To handle Missing Information Without Using NULL
by Hugh Darwen
http://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf

that's when I came up with
this. However, now I'm wondering if I should have a table for each category
containing both the general product fields and its corresponding category
fields.

For a good example IMO on subclassing in SQL, see:

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

Jamie.

--

 

[kristinem via AccessMonster.com]

Oops, not individual tables. I think subclassing is a little over my head at
this point and right now the data isn't all that complex. But I will keep
that in mind for a future version if I ever need to.

 

[kristinem via AccessMonster.com]

Thanks for the information! Even though I don't know if I'll use subclassing
at this point in time, I'm still interested in learning about the method so I
think these will be helpful

At first I thought of throwing everything into one database, but it didn't
seem right having all those null fields

I think you should trust your intuition here .[For the record, I find
John Vinson's "one-to-one relationships are very rare" position to be
counter intuitive e.g. AFAIK everyone has one and only one birth
mother <g>.] For further inspiration, see:

How To handle Missing Information Without Using NULL
by Hugh Darwen
http://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf

that's when I came up with
this. However, now I'm wondering if I should have a table for each category
containing both the general product fields and its corresponding category
fields.

For a good example IMO on subclassing in SQL, see:

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

Jamie.

--

 

[John W. Vinson]

[For the record, I find
John Vinson's "one-to-one relationships are very rare" position to be
counter intuitive e.g. AFAIK everyone has one and only one birth
mother <g>.]

Typically that's a one-to-many relationship, viewed from the "many" side: one
woman may have zero, one, or more offspring, each person has one and only one
birth mother. Or am I misinterpreting?

John W. Vinson [MVP]

 

[John Nurick]

one
woman may have zero, one, or more offspring, each person has one and only one
birth mother.

Not necessarily<g>: we're told the goddess Athena had a birth father.

 

[John W. Vinson]

Not necessarily<g>: we're told the goddess Athena had a birth father.

.... who had a real thunderbolt-scale headache for a long time after!

John W. Vinson [MVP]

 

[Jamie Collins]

Typically that's a one-to-many relationship, viewed from the "many" side: one
woman may have zero, one, or more offspring, each person has one and only one
birth mother.

Consider three tables: birth mothers (one to many, you got me <g>),
women known to have no offspring and women for whom it is not known
whether they have any offspring.

Jamie.

--