Re: Both sides of join?
From: --CELKO-- (jcelko212_at_earthlink.net)
Date: 12/30/04
- Next message: Ivan Arjentinski: "Re: Capturing database size on a schedule and graphing?"
- Previous message: Rick Strahl [MVP]: "Re: UTF-8 Conversion in SQL"
- In reply to: janetb: "Re: Both sides of join?"
- Next in thread: Jeff Williams: "Re: Both sides of join?"
- Messages sorted by: [ date ] [ thread ]
Date: 29 Dec 2004 16:06:01 -0800
If you had keys in the DDL, had tried to follow ISO-11179 naming
conventions and done minimal research on the proper sizes and industry
standards for your data elements, would your schema look like this?
Why do you have CHAR(100) and CHAR(500) columns? Can you explain what
"type_id" is, as opposed to a mere "type" in terms of a meaningful data
model and ISO standards?
Why did you use an IDENTITY in every table but not one key? You have no
standard codes in your industry?
In a culture that reads left-to-right, why did you think that RIGHT
OUTER JOINs would be clearer and easier to maintain?
Nothing in your design makes sense. Given a bad schema, your queries
will always stink. This is the real reason you are not getting much
help in this newsgroup. We can diddle the code a bit, but that is not
going to help in the long run.
CREATE TABLE Disciplines
(disc_id INTEGER NOT NULL PRIMARY KEY, -- no industry standards?
discipline_descr VARCHAR(25) NOT NULL);
CREATE TABLE Specialties
(specialty_id INTEGER NOT NULL PRIMARY KEY, -- no industry standards?
disc_id INTEGER NOT NULL, -- references another table?
specialty_descr VARCHAR(100) NOT NULL, -- huge!
calc_type INTEGER);
CREATE TABLE ExitReasons
(reason_id INTEGER NOT NULL PRIMARY KEY, -- no industry standards?
reason_descr VARCHAR(50) NOT NULL);
Reports are done in the front end of a tiered architecture, NEVER in
the backend. So what does this table and the next one mean? How much
careful research did you do to determine that a line_title gets to
CHAR(500)? Probably none at all.
CREATE TABLE ReportTitles -- what is the key?
(report_id INTEGER NOT NULL PRIMARY KEY,
tbl_id INTEGER NOT NULL, -- metadata? References another table?
order_id INTEGER NOT NULL, -- References another table?
line_title VARCHAR(500) NOT NULL); huge!
CREATE TABLE Reports
(report_id INTEGER NOT NULL PRIMARY KEY, --or a reference??
tbl_id INTEGER NOT NULL, -- metadata? References another table?
title_id INTEGER NOT NULL, -- references another table?
specialty_id INTEGER NOT NULL, -- references another table?
nbr DECIMAL(18, 2) NOT NULL, -- useless name
nbr_type INTEGER NOT NULL, -- even more useless name
reason_id INTEGER, -- references another table?
contact_id INTEGER NOT NULL -- references another table?
);
- Next message: Ivan Arjentinski: "Re: Capturing database size on a schedule and graphing?"
- Previous message: Rick Strahl [MVP]: "Re: UTF-8 Conversion in SQL"
- In reply to: janetb: "Re: Both sides of join?"
- Next in thread: Jeff Williams: "Re: Both sides of join?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
