|
|
Posted by Hugo Kornelis on 04/24/06 02:12
On Mon, 24 Apr 2006 00:46:02 +0200, andro wrote:
>Hugo !
>
>Thank you very much for your help.
>(I've noticed that such a simple question is not so simple to resolve with
>basic SQL knowledge such as mine).
>It is difficult to use first approach by entering everytime TBL_NAME (system
>here).
Hi Andro,
I'm sorry, I don't understand this part of your message.
>
>Is there any easier way - by using table names as "parameters"?
There is, but it's not recommended, because of the security
implications. If you want to read about the method *and* the risks it
has, go to http://www.sommarskog.se/dynamic_sql.html.
>
>I like your second approach.
>Would you be more specific about "extra column (***part of the primary
>key***)"
>What do you mean by "part of the primary key"?
That would be easier to answer if you had told me exactly how yoour
tables currently look.
I'll use a made-up example to illustrate this. Consider the following
(bad!) design for daily sales data:
CREATE TABLE North_Sales
(SaleDate smalldatetime NOT NULL,
ProductNo int NOT NULL,
AmountSold int NOT NULL,
PRIMARY KEY (SaleDate, ProductNo),
FOREIGN KEY (ProductNo) REFERENCES Products
)
CREATE TABLE East_Sales
(SaleDate smalldatetime NOT NULL,
ProductNo int NOT NULL,
AmountSold int NOT NULL,
PRIMARY KEY (SaleDate, ProductNo),
FOREIGN KEY (ProductNo) REFERENCES Products
)
(and two more for regions South and West)
These four tables can and should be replaced by this single table:
CREATE TABLE Sales
(SaleDate smalldatetime NOT NULL,
Region char(5) NOT NULL,
ProductNo int NOT NULL,
AmountSold int NOT NULL,
PRIMARY KEY (SaleDate, Region, ProductNo),
FOREIGN KEY (ProductNo) REFERENCES Products,
CHECK (Region IN ('North', 'South', 'East', 'West'))
)
Notice how I added a column "Region", _AND* added this column to the
list of columns that make up the primary key.
(I also added a check constraint - this should be replaced by a FOREIGN
KEY constraint if there's a Regions table in the database as well).
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|