Is it a good idea to have two tables with the same primary key?

For example, I have one table which has "QID" as its primary key - this
table contains fields which are almost always use for each record. I also
have a second table, but the difference is that the fields only occasionally
get used. Obviously it is inefficient to have all these secondary fields in
the same table as the always-used fields, so I have created a second table
with the same QID as PK in order to represent the 1 to 0...1 relationship
between the tables.

Dave wrote:
Hi,

You need to find out about normalisation. Don't worry about efficiency
at this stage. It is far more important to have good data integrity. You
should be able to design all of your tables such that there are no nulls
or "blanks" anywhere in them. This will probably mean that you will end
up with lots of tables with the same primary key. You can then join them
back together as and when it is necessary for queries.

John

Using normalization correctly, you should almost never have 2 tables
with the same primary key .I say almost because I'm sure someone can
come up with an unusual situation to have one. But as a rule, this
should not happen.

As far as putting in seldom used field in the same table, that should
not effect your effecieny at all. Just try avoiding:

SELECT * FROM table

Instead, list only the fields you need in each situation.

If anything, having 2 tables with the same key will decrease your
effecieny because both table have to maintain the same primary key
index and you'll have to setup up additional RI between them.

In short, put all related fields, which relate to each other in a 1-1
relationship, in the same table.

On 3 Jun 2006 09:35:10 -0700, "scubakiz" <markk (AT) kizinc (DOT) com> wrote:

A one-to-maybe-one is the case. PK refers to main data in one
table and the conditional data in the other.

If that is what is needed, then that is what is needed. As a
concern, efficiency comes after getting it right.

If they are so related.

Sincerely,

Gene Wirchenko

Dave wrote:
There's your problem. You think the pseudo-key is the real key.

No example of the fields that are "always used versus sometimes used.
If that is the ONLY criteia for the split, then you are not normalizing
your tables properly. But if the other table represents a specialized
instance, then you are likely okay. For example

a work order table contains basic information for customer, WO#,
payment info and so on. But certain warranties are paid by a third
party (It's your car, but GM is paying for a recall repair.) A second
table could be used to store this information, with the same primary
key being the work order number (WO#).

HTH,
ed