You are here: Re: Table Joins on more than one field « MsSQL Server « IT news, forums, messages
Re: Table Joins on more than one field

Posted by --CELKO-- on 05/10/06 01:13

>> Can someone help me with this SQL? <<

Not really, because you do not have an RDBMS. You have a bunch of
poorly designed non-tables written in SQL.

>> 1) EMPLOYEE table has columns 'employee_id' and 'emp_sid' and other
columns <<

The table name EMPLOYEE (all uppercase so it ihard to read or you are
using punchcards for the one employee you have -- singular names mean
one entity). If this table is for personnel data (a set), not for each
employee as if they records in a sequential file, why did you give it
such a bad name. Which one of these two columns is the PRIMARY KEY?
Oh, you have no keys at all!!

>> 2) EMPLOYEE_BENEFITS table has a column called employee_entity, this column can be joined to either 'employee_id' OR 'emp_sid' but not both in the EMPLOYEE table. <<

You actually used a postfix of entity! So much for data modeling and
ISO-11179 specs! You also missed the whole idea of PK-FK constraints --
There is no OR option in the concept. I think that some early network
DBMS system had "variant pointers" that could work liekthat, but I
owuld have to research it

>> 3) EMPLOYEE_TRACK table has column called employee_track_entity, this column can be joined to the employee_benefits_id (PK) of the EMPLOYEE_BENEFITS table.<<

Again, a singular name so we have only one track. Since IDENTITY can
never be a key by definition, EMPLOYEE_BENEFITS has no key to
reference. Don't your benefit programs have names, tax ids, or
something you can validate and verify?

Again, you are creating a pointer chain DBMS system in SQL, but do not
have the background to realize that you are re-inventing a square
wheel.

CREATE TABLE Personnel
(employee_id CHAR(9) NOT NULL PRIMARY KEY, -- use legally required id
...);

CREATE TABLE EmployeeBenefits
(employee_benefits_id INTEGER NOT NULL PRIMARY KEY,
employee_id CHAR(9) NOT NULL
REFERENCES Personnel(employee_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE EmployeeTracks
(employee_track_id INTEGER NOT NULL PRIMARY KEY,
.. );

>> The above SQL I wrote is this: the second inner join uses a OR to join either of the columns in the first table EMPLOYEE. There is performance degradation with this SQL. With huge data It takes about 30 seconds to execute. <<

You are worried about performance degradation?? You forgot the lack of
data integrity caused by two keys.

>> I know this is not the perfect way to do it, can anyone of the SQL Gurus please enlighten me to a faster approach? <<

Clean up the DDL. Get real keys instead of that IDENTITY crap. Learn
basic data modeling. Fix the multiple key problem. And stop putting
SELECT * in production code.

I saw a problem like this once a few decades ago. Two companies had
merged because they wer tired of competing in the same market (welding
supplies, same products). One company coded its inventory by the
location in the warehouse (makes picking orders very easy) and the
other coded by the type of welding done (aluminium, brass, underwater,
etc.). The warehouses were arranged very differently becuase of the
encoding. Are you familar with the Japanese housing numbering system
versus the United States?

They wanted a combined inventory and catalog, but their customers and
personnel were too used to one system or the other and the politics
were awful.

What they needed as a kludge was one and only one SKU code and a
conversion table in the computer and a pair of codes on the labels.
Until they could design a good SKU code.

Did this cost a lot of time and money? You bet! In fact, it killed the
merger. Each warehouse was an island of data, so there was no timely
way to move inventory across the two SKU codes to fill orders.
Someone asks for 5 Widgets and 2 are under code #A and 3 are under code
#B in another warehouse, but nobody knew!

Have you been to a Barnes & Noble lately? Look at the company sticky
label that goes over the pre-printed ISBN code. Same expensive, stupid
design flaw that you and the welding supplies companies had.

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация