sql server programming: Enterprise manager table design -- sql server programming

Re: Enterprise manager table design

Uri Dimant
11/7/2005 12:00:00 AM

Hi

I prefer to write a script does the job .Don't forget to drop first
constraints and then re-create with DEFAULT (1)

[quoted text, click to view]

RE: Enterprise manager table design

John Bell
11/7/2005 12:03:36 AM

Hi

To add to Uri's suggestion, you can script the table by right clicking the
table in EM and choose the Generate SQL option. Then when you have the file
it can be edited (possibly in Query Analyser!) where global replacement could
save you time. You can then run the script in Query Analyser or from a
command prompt using OSQL.

If you don't want to loose the data from the table then you may want to copy
the contents into a temporary table and then load them back if you are using
the drop/create method.

John

[quoted text, click to view]

Re: Enterprise manager table design

Hugo Kornelis
11/8/2005 12:49:27 AM

[quoted text, click to view]

Hi nywebmaster,

This question suggests that you have a repeating group in your table.
That is a sign of a weak design. I'll give you an example:

Weak design:
CREATE TABLE SalesPersons
(SalesPersonID int NOT NULL
SalesPersonName varchar(50) NOT NULL,
SalesJan decimal(9,2) DEFAULT NULL,
SalesFeb decimal(9,2) DEFAULT NULL,
SalesMar decimal(9,2) DEFAULT NULL,
SalesApr decimal(9,2) DEFAULT NULL,
SalesMay decimal(9,2) DEFAULT NULL,
SalesJun decimal(9,2) DEFAULT NULL,
SalesJul decimal(9,2) DEFAULT NULL,
SalesAug decimal(9,2) DEFAULT NULL,
SalesSep decimal(9,2) DEFAULT NULL,
SalesOct decimal(9,2) DEFAULT NULL,
SalesNov decimal(9,2) DEFAULT NULL,
SalesDec decimal(9,2) DEFAULT NULL,
PRIMARY KEY (SalesPersonID)
)

Better design:
CREATE TABLE SalesPersons
(SalesPersonID int NOT NULL
SalesPersonName varchar(50) NOT NULL,
PRIMARY KEY (SalesPersonID)
)
CREATE TABLE Sales
(SalesPersonID int NOT NULL,
MonthNo tinyint NOT NULL,
Sales decimal(9,2) NOT NULL,
PRIMARY KEY (SalesPersonID, MonthNo)
FOREIGN KEY (SalesPersonID)
REFERENCES SalesPersons (SalesPersonID)
CHECK (MonthNo BETWEEN 1 AND 12)
)

Best, Hugo
--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.