Six one to one related tables: question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jake - 27 May 2005 06:39 GMT

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

Reply to this Message

Tim Ferguson - 27 May 2005 17:29 GMT

"jake" <jake.roseberry@gmail.com> wrote in news:1117172394.437496.73010
@g14g2000cwa.googlegroups.com:

> 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

Reply to this Message

Alex White MCDBA MCSE - 27 May 2005 17:35 GMT

Sounds like normalisation gone mad, seen it before.

how many fields are there in each of these tables?

Signature

Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

> "jake" <jake.roseberry@gmail.com> wrote in news:1117172394.437496.73010
> @g14g2000cwa.googlegroups.com:
[quoted text clipped - 15 lines]
>
> Tim F

Reply to this Message

jake - 28 May 2005 04:30 GMT

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?

Reply to this Message

tina - 28 May 2005 08:08 GMT

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

> My funeral home database currently has one large table"Vitals" (as well
> as several others,survivors, finance, to do list- all related to the
[quoted text clipped - 8 lines]
>
> What is the right answer to this... one big one or something else?

Reply to this Message

Tim Ferguson - 28 May 2005 09:50 GMT

"jake" <jake.roseberry@gmail.com> wrote in news:1117251025.957692.84480
@g47g2000cwa.googlegroups.com:

> 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

Reply to this Message