Orphan Table in a schema

[Ayat]

Hello,

I have some questions about a schema design I am working on. After much trial, it appears to me that a particular table is not and probably does not need to be related to any other table in the schema. So my questions are:

1. Is it right or quite typical for schemas to have some tables which are orphan (i.e. unattached to any other tables)?

2. If tables can exist in isolation, then what are the primary purpose they provide? I have a suspicion that if fields from an orphan table are used in query etc, then the possibility of Cartesian joins will arise. If so, how does one take care of avoiding such a problem?

Thanks very much for the enlightenment.

 

[Pat Hartman]

I can't think of a reason to have an orphan table. None of my "client" databases have them but I have some in databases I use for myself. For example, in a database I created that documents other databases, I have a table to hold relationships. I created this table so I would have a place to store the relationship info as I gathered it. The purpose was to create a form that could show the COMPLETE path to each reference since the built in dialog cuts off this information making it difficult to work with. There would never be a reason to join this table to any others in the database so it is an "orphan".

If you forsee the need to join to an "orphan" table, then it is not truely an orphan and so it needs a field on which you can join to it. Once that field is added, a relationship should be made and the table is no longer an orphan.

Yes, a join that doesn't connect a pk to a fk will create a Cartesian Product. Tell us more about this orphan table.

 

[Ayat]

thanks for the reply Pat.

I am attaching a word doc with part of my schema that is relevant to the issue as well as a description of the problem. This might shed light on why I am seeing the need for an orphan table. May be you can think about modeling this problem another way. Thanks again.

==========

I am working on designing an application in Access that produces
reports of how the various Products/Projects in an organization are
being planned out in terms of their launch. The products can be in
the concept, engineering or complete status and for each status a
regular (typically monthly) feed of release/launch data (Beta and GM
dates) is provided by the respective teams. The idea is to produce a
release schedule for all the products based on these dates and
publish them in the monthly (could be different frequency depending
on the organization's need) calendar. From month-to-month, these
release/launch dates can slip and if there is a slip from the
previous version of the launch calendar that was published, then the
change needs to be displayed in the calendar report. I have designed
most of the schema and feel (hope) I am mostly there. However, the
critical piece is how to design a schema that can incorporate
incoming release data any time of the month, keep a track of the
incoming data for any future ad-hoc reporting purposes, and use the
latest release date data and the dates used in the previous calendar
to produce the next calendar (showing the latest dates and the
slippage if any).

I want to incorporate the current launch dates and show slippages

1. I have put the release/launch date information in the
LAUNCH_DETAILS table

2. since the Calendar can actually be published anytime, I have made
a separate CALENDAR_VERSION table. Need to figure out how to go about
extracting the lates release date and checking whether it slipped
from what was reported in the last calendar and reflecting that
information in the upcoming calendar

 

[Pat Hartman]

If the information for the launch date originates in the calendar, that's where it should stay. It shouldn't be stored in the launch details table. Just store a reference to the calendar.

 

[Ayat]

Pat,

As explained in my posting, information for the launch date is gathered for each product and is reported in the calendar. It does not originate in the calendar. Think of data about launch dates of various products trickling in (i.e getting updated) throughout the month. Calendar is just a snap shot of those dates at a given point in time. So actually Calendar date is nothing more than just the date that the Calendar is published.

Hope this clears it up. Now do you see the a reason for the orphan calendar table. Any other comments on the schema?

Thanks again

 

[Pat Hartman]

No, I don't see it. Is the calendar a recap of the dates in the launch table?