Re: Newbie help
From: Joe Celko (jcelko212_at_earthlink.net)
Date: 09/20/04
- Next message: Anith Sen: "Re: update a row in one table with values from muliple rows in another table"
- Previous message: Louis Davidson: "Re: dbo or Role as owner of database tables?"
- In reply to: Steve: "Newbie help"
- Next in thread: Steve: "Re: Newbie help"
- Reply: Steve: "Re: Newbie help"
- Reply: Steve: "Re: Newbie help"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 20 Sep 2004 15:14:12 -0700
>> I was hoping that I might get some feedback on it. <<
Always a bad thing to ask for in this newsgroup :)
>> The purpose of the procedure is to populate a single record [sic]
with the most recent (as compared to the time parameter) non-null values
for each column in the specified table. <<
What you are doing is writing metadata tools in SQL and you are not
supposed to do that in an application.
The whole idea of this procedure is a violation of basic software
engineering principles. This is **much** more fundamental than SQL
programming. Don't you remember all that stuff about cohesion and
coupling?
Did you mean "as determined by the time parameter"? I do not see such a
parameter; if it were done properly it would be a temporal data type and
not a string. Dynamic SQL is a sign of failure on the part of the DB
programmer, so he has to let the end user build the system he was not
able to.
The right answer is never pass a table name as a parameter. You need to
understand the basic idea of a data model and what a table means in
implementing a data model. Go back to basics. What is a table? A model
of a set of entities or relationships. EACH TABLE SHOULD BE A DIFFERENT
KIND OF ENTITY. What having a generic procedure works equally on
automobiles, octopi or Britney Spear's discology is saying that your
applications a disaster of design.
1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.
2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future. Remember the
basics of Software Engineering? Modules need weak coupling and strong
cohesion, etc.
3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal. Look up what
Chris Date has to say about this design flaw.
4) You might have failed to tell the difference between data and
meta-data. The SQL engine has routines for that stuff and applications
do not work at that level, if you want to have any data integrity.
Yes, you can write a program with dynamic SQL to kludge something like
this. it will last about a year in production and then your data
integrity is shot.
You use square brackets, global temporary table, TOP and other
proprietary syntax edven when standard syntax is allowed. Your names
violate ISO-11179 naming rules with those silly 1960's BASIC prefixes.
--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. Sample data is also a good idea, along with clear
specifications.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
- Next message: Anith Sen: "Re: update a row in one table with values from muliple rows in another table"
- Previous message: Louis Davidson: "Re: dbo or Role as owner of database tables?"
- In reply to: Steve: "Newbie help"
- Next in thread: Steve: "Re: Newbie help"
- Reply: Steve: "Re: Newbie help"
- Reply: Steve: "Re: Newbie help"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
