Six one to one related tables: question

[jake]

My project has six table that are related "one to one"
What is the proper way to combine them so I can use them for my form.

Currently If I try to combine them in a query I get the "ambigous outer
joins" error message

Thanks Jake

 

[Tim Ferguson]

My project has six table that are related "one to one"
What is the proper way to combine them so I can use them for my form.

Almost certainly, the answer is "just don't do that!" One to one
relationships are pretty rare in a well designed database -- what is the
reason for having six of them in your design?

In a typical sub-typing design, I usually let the user pop up one of
several dialogs according to the subtype chosen; a set of tab containers
might be another way to do it. The best choice in any UI is dictated by the
business process and your users' way of working.

Best wishes



Tim F

 

[Alex White MCDBA MCSE]

Sounds like normalisation gone mad, seen it before.

how many fields are there in each of these tables?

 

[jake]

My funeral home database currently has one large table"Vitals" (as well
as several others,survivors, finance, to do list- all related to the
Vitals table in a one to many relationship. Vitals contains 200 or so
fields .
It includes everything that our funeral home keeps track of during the
funeral. From the data needed to fill out the forms, obit, etc. to the
service info.

I trying to revamp it my database design...so its normalized...but that
is when I get the 6 one to one relationship issue ...

What is the right answer to this... one big one or something else?

 

[tina]

well, i'd have to say your data normalization is still suspect. it's hard to
imagine a single entity with 200 unique, non-repeating data elements. do you
have any fields named something like

Choice1
Choice2
Choice3

the above is an example of a common normalization error: putting data
elements into field *names*. it's usually a sign of data that should be in a
separate table. other examples might be multiple fields for phone numbers,
or multiple sets of fields for addresses, or multiple sets of fields for
names of people.

suggest you review your table yet again with above in mind. if you find
fields you think may fit the above description, but aren't sure, you can
post a partial list (with appropriate explanations if the fieldnames are too
esoteric) and request comments.

hth

 

[Tim Ferguson]

Vitals contains 200 or so
fields .
It includes everything that our funeral home keeps track of during the
funeral. From the data needed to fill out the forms, obit, etc. to the
service info.

I am with Tina: as many as sixty fields in a table is pretty rare; twenty
or so is a usual maximum; 200 is diagnostic of a non-normalised design.

Paper forms are always a poor place to start -- you do need to be looking
at the entities involved in the data themselves.

Best of luck


Tim F