I have 3 tables
Person
Period
Calendar

Calendar 1~~~~n Period
Person 0-1~~~~1 Calendar

Because a Calendar can be associated with another entity that is not a
person, I can't have a Person/Period relation. So I have a Calendar table
that just has one column which is both the pkey of the Calendar and the Pkey
of the Person.

Struck me as odd. Is that okay?

--
Mike W

On Dec 8, 7:39 am, "visionset" <s... (AT) ntlworld (DOT) com> wrote:
It strikes me as not okay, just because you said the same attribute is
the PK of BOTH the Calander and the Person tables.
How about giving some context. What business requirement are you trying
to fulfill? How about some clue about the attributes on the tables.

PERIOD
startdate date FK to CALENDER eachday
desc char --- first quarter, january period, holiday period
....
others
PK startdate, desc

CALENDER
eachday date
PK eachday

PERSON
day date FK to CALENDER eachday
name char
others
PK day --- This table seems misnamed at least.

There's nothing wrong really with a table that is all PK, but it is
very unusual. Would you really need the table when you get to the
physical design? You might be able to merge it with one fo the other
tables, but you need to know the business case this group of tables
attempts to solve to really know what to do.

In short your question cannot be answered as you posed it. (Well yes it
can, but with wrong answers more likely than right ones.) I, for one,
would seriously question a single column table in a design review.
Better questions get better answers.

HTH,
ed

"Ed Prochak" <edprochak (AT) gmail (DOT) com> wrote



Yeah was just to post a follow up.

I'll rephrase it in another possible design:

Period (pid, start_date, end_date, nullable_person_pid,
nullable_company_pid)
Person(pid, name)
Company(pid, name)

This time without the Calendar that I initially created to try to improve
the design.

Now the one or none is moved to the Period and two fkey columns where one or
the other is null for
a specific Period.

Perhaps the nasty nulls are the best I can hope for.

I don't want a separate table for company and person related periods, that
really would be yuk.

Thanks for your input
--
Mike W

visionset wrote:
The "Period" table you show *looks* like it means to relate a person to a
company for a given time interval, but then you talk about either the
"person_pid" or the "company_pid" being NULL, so I gather you mean a given
period should relate either to "person" or "company", but not both at the same
time.

If I have sussed out your goal correctly, then you are not quite yet where you
want to go.

How about:

CREATE TABLE person
(
person P_TYPE ... PRIMARY KEY,
.... );

CREATE TABLE company
(
company C_TYPE ... PRIMARY KEY,
.... );

CREATE TABLE event
(
event E_TYPE ... PRIMARY KEY,
start DATETIME NOT NULL,
finis DATETIME,
.... );

CREATE TABLE personevent
(
person P_TYPE NOT NULL REFERENCES person (person),
event E_TYPE NOT NULL REFERENCES event (event),
PRIMARY KEY ( person, event )
);

CREATE TABLE companyevent
(
company C_TYPE NOT NULL REFERENCES company (company),
event E_TYPE NOT NULL REFERENCES event (event),
PRIMARY KEY ( company, event )
);

I am intentionally glossing over the choice of "correct" type for the main
tables' primary keys. Those keys could well be multi-column keys based on
"natural" attributes of the modeled entities. Whatever you choose, obviously
the corresponding foreign keys should have the same structure.

- Lew

"Lew" <lew (AT) nowhere (DOT) com> wrote



Thanks Lew, that is spot on.

--
Mike W