Database Design Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Database Design Issue

Author

Topic

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2005-05-13 : 11:58:00

Hello,
I have a database design issue and I will once again use your expert advice in making the trade offs between the two solutions I thought of.

Here is the problem ...
We have a table for Standards. We get these standards from an outside company. And they keep sending us updated/new standards once in a while. Now we want to use our own custom standards.

Currently this Standards table (the one we are getting from outside company) is not that huge. It has half a million rows. And it might grow to a million rows within 2 to 3 years.

If we start using the Custom Standards table this table might grow to half a million rows in 3 years.

So I thought of two solutions.
1) Create a similar table structure as Standards. And then use that to add our own custom standards. And in the application whenever we are looking for standards, the application will decide which table to use. The problem with this is my application has to know which table to call.
2) Using Partitioned view. So I will create a Partitioned View on these two tables. The advantage of this having standards and customStandards table is transparent to my application. But the problem with this is I have to do a UNION ALL every time. And also as my data is not that huge, is having partitioned views is an overkill in my case.

Which one of the above two solutions will have better performance?
Is there any other better solution?

Thanks for your advice,
maximus

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-13 : 13:35:05

1 table properly indexed

Post the DDL

Brett

8-)

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2005-05-13 : 14:13:13

Hi Brett,
This is the scehma for the original Table.(This is the one we update when we get data from other company)
Standards(Standard_ID(PK),Std_Title,Std_descr,Std_Stem)

Soultions:
1) For this solution I want to create another table like this
Custom_Standards(Standard_ID(PK),Std_Title,Std_descr,Std_Stem)
And keep adding all my custom Standards info in this table.

2) For this solution I want to do this

Standards(Standard_ID(PK),Std_Title,Std_descr,Std_Stem,Type)
Custom_Standards(Standard_ID(PK),Std_Title,Std_descr,Std_Stem,Type)

The field Type will tell me whether it is first table or the second table.
So now I will do this
ALTER TABLE Standards ADD CONSTRAINT CK_Std_Type CHECK (Type = 1)
ALTER TABLE Custom_Standards ADD CONSTRAINT CK_Cust_Std_Type CHECK (Type = 2)

And then I will create a partitioned view like this

CREATE VIEW All_Standards AS
SELECT * FROM Standards
UNION ALL
SELECT * FROM Custom_Standards

Another variation for my second solution is for this new CustomStandards table I can have IDENTITY for Standard_ID but it will start with -1 and increment will be -1.

So this is what it looks like

Standards(Standard_ID(PK),Std_Title,Std_descr,Std_Stem)
Custom_Standards(Standard_ID(PK),Std_Title,Std_descr,Std_Stem)

ALTER TABLE Standards ADD CONSTRAINT CK_Std_Type CHECK (Standard_ID > 0)
ALTER TABLE Custom_Standards ADD CONSTRAINT CK_Cust_Std_Type CHECK (Standard_ID < 0)

And then I will create a partitioned view like this

CREATE VIEW All_Standards AS
SELECT * FROM Standards
UNION ALL
SELECT * FROM Custom_Standards

Is this info enough?
Thanks again,
maximus

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2005-05-16 : 10:47:19

Any Ideas?

Thanks
Maximus

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources