Table Design Question

If, after analysis, I determine that two tables have the same primary
key, does that necessarily indicate that I really have two parts of the
same table and they should be collapsed into one?

Thank you,

-- Rick

re: Table Design Question

Guinness Mann wrote:
[color=blue]
> If, after analysis, I determine that two tables have the same primary
> key, does that necessarily indicate that I really have two parts of the
> same table and they should be collapsed into one?
>
> Thank you,
>
> -- Rick[/color]

My 2 cents:

Logically, yes. However, there may be practical reasons in some cases
that you might want it as-is. For instance, if one table's data is
constantly used, and you want it all in cache for performance, and the
other table has a lot of secondary data, which you access much more
rarely, perhaps only for a given row, then you might make a case for
the status quo.

Joe Weinstein at BEA

re: Table Design Question

In article <3FDDF004.8030507@bea.com>, joeNOSPAM@bea.com says...[color=blue][color=green]
> > If, after analysis, I determine that two tables have the same primary
> > key, does that necessarily indicate that I really have two parts of the
> > same table and they should be collapsed into one?[/color]
>
> Logically, yes. However, there may be practical reasons in some cases
> that you might want it as-is. For instance, if one table's data is
> constantly used, and you want it all in cache for performance, and the
> other table has a lot of secondary data, which you access much more
> rarely, perhaps only for a given row, then you might make a case for
> the status quo.[/color]

I was using identity fields as primary keys in both tables and after
listening to Joe Celko's rants about how evil that is I went to some
trouble to identify natural primary keys. To my surprise, both tables
had the same natural keys.

The second table is composed of large nvarchar attributes (nvarchar
(1200)) whereas the first is quite narrow, but both tables are always
accessed at the same time (I didn't know that would be the case when I
was designing the tables), so I think it would be a net savings to
consolidate them.

Thanks,

-- Rick

re: Table Design Question

>> was using identity fields as primary keys in both tables and after
listening to Joe Celko's rants about how evil that is I went to some
trouble to identify natural primary keys. To my surprise, both tables
had the same natural keys. <<

Unverifiable exposed surrogates tend to hide the real data model like
that. But the question to ask is, "What entity or relationship does
each table model?"

If they are two logically different things, then it makes sense to have
two tables. Example: I use a VIN number for both automobiles and
trucks, but have different rules for how I handle each type of vehicle
in the company (maintenance, depreciation, accounting for mileage, etc.)

If the tables are really two subsets of attributes for the same entity,
then they need to be in one table. Example: one table has the payroll
computation data and another table has the payroll mailing address for
each employee.

If the tables are really one attribute that has been split on a value,
then they need to be put back together. Example: one table shows when
someone arrived at work, and another table shows when they left work.
The real attribute is the duration on the job.

With a long text column like that, I'd suspect that you have a comment
that belongs to the entity in the first table,

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Table Design Question

In article <3fde2ede$0$203$75868355@news.frii.net>,
joe.celko@northface.edu says...[color=blue]
> Unverifiable exposed surrogates tend to hide the real data model like
> that. But the question to ask is, "What entity or relationship does
> each table model?"
>
> If they are two logically different things, then it makes sense to have
> two tables. Example: I use a VIN number for both automobiles and
> trucks, but have different rules for how I handle each type of vehicle
> in the company (maintenance, depreciation, accounting for mileage, etc.)[/color]

Joe, Do you talk about these issues (autonumber vs natural keys, etc)
in your "SQL for Smarties" book? Are there other references you
recommend?

[color=blue]
> With a long text column like that, I'd suspect that you have
> a comment that belongs to the entity in the first table,[/color]

What I have is a test question database for an educational application.
I had hoped to get some re-use of questions and distractors, and I *did*
get about a 2-1 re-use of the distractors. Strangely enough, when I put
the distractor text into the table instead of an auto-number distractor
ID, the database actually shrunk and got faster. I noticed that whereas
previously my indexes took up 30% to 40% as much space as the data, now
they're less than 10%. Of course I've simplified a little bit here, but
I guess you never know until you experiment.

--Rick

re: Table Design Question

>> Joe, Do you talk about these issues (autonumber vs natural keys, etc)
in your "SQL for Smarties" book? Are there other references you
recommend? <<

SQL FOR SMARTIES is really a bunch of programming tricks; DATA &
DATABASES is philosophy and foundations. Frankly, the best discussions
are on the newsgroups. I keep harvesting material for a second edition
of DATA & DATABASES from them.
[color=blue][color=green]
>> What I have is a test question database for an educational[/color][/color]
application. <<

There are packages for that kind of application; just get any
educational trade magazine and look at the ads. Q&A stuff is text, and
SQL does nto do well with text and semantics.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!