Referential Integrity practices for complex Database

I am a database Engineer for a company that has relatively large and complex
SQL Server databases.
The DB Engineers group seem to have different opinions about weather or not
we should enforce referential integrity to our database.(In fact we have
databases that are both ways). Both aspects seem to have pro's and cons.
I am trying to find out what are the common practices adopted by other
companies in similar situation. Do you enforce the referential integrity? If
so how do you deal with the heightened complexity of back end updates when
necessary? On the other hand if you did not, how do we enforce accuracy?
Your input in this is very much appreciated.Hi
Wow , I thought that DB Engineer would not has any doubt for this question.
In my company i have always been creating a primary key to every table and
foreign key to enforce the referential integrity. Benefits? your querie's
perfomance will be improved , it will prevent from unwanted deletion ,
create a diagram to see a whole picture of your database's relatioship
and......
Well , BOL has pretty good description about andvatages to enforce the
referential integrity.
"bluefish" <bluefish@.discussions.microsoft.com> wrote in message
news:72BAFD60-A99A-4CE9-83D1-C52C78BE8B38@.microsoft.com...
>I am a database Engineer for a company that has relatively large and
>complex
> SQL Server databases.
> The DB Engineers group seem to have different opinions about weather or
> not
> we should enforce referential integrity to our database.(In fact we have
> databases that are both ways). Both aspects seem to have pro's and cons.
> I am trying to find out what are the common practices adopted by other
> companies in similar situation. Do you enforce the referential integrity?
> If
> so how do you deal with the heightened complexity of back end updates when
> necessary? On the other hand if you did not, how do we enforce accuracy?
> Your input in this is very much appreciated.
>|||bluefish wrote:
> I am a database Engineer for a company that has relatively large and complex
> SQL Server databases.
> The DB Engineers group seem to have different opinions about weather or not
> we should enforce referential integrity to our database.(In fact we have
> databases that are both ways). Both aspects seem to have pro's and cons.
> I am trying to find out what are the common practices adopted by other
> companies in similar situation. Do you enforce the referential integrity? If
> so how do you deal with the heightened complexity of back end updates when
> necessary?
The purpose of referential integrity is to implement business rules and
ensure that your database accurately and consistently models the real
world. The fact that your engineers have to ask this question suggests
that either they don't understand your business or that they don't care
about data integrity.
> On the other hand if you did not, how do we enforce accuracy?
Better ask the people who designed your systems without any
integrity...
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Referential Integrity practices for complex Database

I am a database Engineer for a company that has relatively large and complex
SQL Server databases.
The DB Engineers group seem to have different opinions about weather or not
we should enforce referential integrity to our database.(In fact we have
databases that are both ways). Both aspects seem to have pro's and cons.
I am trying to find out what are the common practices adopted by other
companies in similar situation. Do you enforce the referential integrity? If
so how do you deal with the heightened complexity of back end updates when
necessary? On the other hand if you did not, how do we enforce accuracy?
Your input in this is very much appreciated.Hi
Wow , I thought that DB Engineer would not has any doubt for this question.
In my company i have always been creating a primary key to every table and
foreign key to enforce the referential integrity. Benefits? your querie's
perfomance will be improved , it will prevent from unwanted deletion ,
create a diagram to see a whole picture of your database's relatioship
and......
Well , BOL has pretty good description about andvatages to enforce the
referential integrity.
"bluefish" <bluefish@.discussions.microsoft.com> wrote in message
news:72BAFD60-A99A-4CE9-83D1-C52C78BE8B38@.microsoft.com...
>I am a database Engineer for a company that has relatively large and
>complex
> SQL Server databases.
> The DB Engineers group seem to have different opinions about weather or
> not
> we should enforce referential integrity to our database.(In fact we have
> databases that are both ways). Both aspects seem to have pro's and cons.
> I am trying to find out what are the common practices adopted by other
> companies in similar situation. Do you enforce the referential integrity?
> If
> so how do you deal with the heightened complexity of back end updates when
> necessary? On the other hand if you did not, how do we enforce accuracy?
> Your input in this is very much appreciated.
>sql

Reference Material Recommendations

I'm a relatively experienced SQL Server 2000/2005 development DBA/Programmer
but I'm up for a new position involving systems that are dealing in 1000s of
user transactions a second and I don't have any experience in that area.
Does anyone have suggestions for reading I can do to familiarize myself with
the specific hot points of that type of system?
Any help is appreciated.
Thanks
RCB
These are not all 2005 specific but might be a good start:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Tempdb in 2005:
http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc
Physical Database Storage:
http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/PhysDBStor.doc
Andrew J. Kelly SQL MVP
"Robert B" <RobertB@.discussions.microsoft.com> wrote in message
news:0C9E5F9D-2F7A-4495-8CE8-018E3C358B9C@.microsoft.com...
> I'm a relatively experienced SQL Server 2000/2005 development
> DBA/Programmer
> but I'm up for a new position involving systems that are dealing in 1000s
> of
> user transactions a second and I don't have any experience in that area.
> Does anyone have suggestions for reading I can do to familiarize myself
> with
> the specific hot points of that type of system?
> Any help is appreciated.
> Thanks
> RCB
|||Thanks for the list of articles, some were familiar to me some had some great
information. Does anyone else know of any articles more about the 'design'
aspects of high transaction databases?
Thanks Again

Reference Material Recommendations

I'm a relatively experienced SQL Server 2000/2005 development DBA/Programmer
but I'm up for a new position involving systems that are dealing in 1000s of
user transactions a second and I don't have any experience in that area.
Does anyone have suggestions for reading I can do to familiarize myself with
the specific hot points of that type of system?
Any help is appreciated.
Thanks
RCBThese are not all 2005 specific but might be a good start:
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Tempdb in 2005:
http://download.microsoft.com/downl...gWithTempDB.doc
Physical Database Storage:
http://download.microsoft.com/downl...
PhysDBStor.doc
Andrew J. Kelly SQL MVP
"Robert B" <RobertB@.discussions.microsoft.com> wrote in message
news:0C9E5F9D-2F7A-4495-8CE8-018E3C358B9C@.microsoft.com...
> I'm a relatively experienced SQL Server 2000/2005 development
> DBA/Programmer
> but I'm up for a new position involving systems that are dealing in 1000s
> of
> user transactions a second and I don't have any experience in that area.
> Does anyone have suggestions for reading I can do to familiarize myself
> with
> the specific hot points of that type of system?
> Any help is appreciated.
> Thanks
> RCB|||Thanks for the list of articles, some were familiar to me some had some grea
t
information. Does anyone else know of any articles more about the 'design'
aspects of high transaction databases?
Thanks Again

Reference Material Recommendations

I'm a relatively experienced SQL Server 2000/2005 development DBA/Programmer
but I'm up for a new position involving systems that are dealing in 1000s of
user transactions a second and I don't have any experience in that area.
Does anyone have suggestions for reading I can do to familiarize myself with
the specific hot points of that type of system?
Any help is appreciated.
Thanks
RCBThese are not all 2005 specific but might be a good start:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Tempdb in 2005:
http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc
Physical Database Storage:
http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/PhysDBStor.doc
Andrew J. Kelly SQL MVP
"Robert B" <RobertB@.discussions.microsoft.com> wrote in message
news:0C9E5F9D-2F7A-4495-8CE8-018E3C358B9C@.microsoft.com...
> I'm a relatively experienced SQL Server 2000/2005 development
> DBA/Programmer
> but I'm up for a new position involving systems that are dealing in 1000s
> of
> user transactions a second and I don't have any experience in that area.
> Does anyone have suggestions for reading I can do to familiarize myself
> with
> the specific hot points of that type of system?
> Any help is appreciated.
> Thanks
> RCB|||Thanks for the list of articles, some were familiar to me some had some great
information. Does anyone else know of any articles more about the 'design'
aspects of high transaction databases?
Thanks Again

Reduce Records Shown From Dimension

Hi There,

I'm relatively new to AS2005, so you'll have to excuse me if there is a simple solution that has been overlooked.

Here is the situation - We have a fact table, linked to several dimensions - one of them being a generic date dimension used by a number of fact tables. The Date Dimension has all dates ranging from 1901 to 2100, but the fact table I am querying just has records from 2005 to present. Once the cube is processed and I open the cube in Excel (for example) it lists every date from the Date Dimension, is there any way to limit which dates are processed into the cube to those that just appear in the fact table (in essence - what would be an Inner Join in TSQL).

Hope this makes sense,

Regards,

Tobias

Are you using the dimension on rows or columns? Or as a filter?

If it is rows or columns you change the setting to not show empty values.

If it is a filter, you could create a set that only contains only those dates that has rows. Regardless, you could probably remove a lot of dates from the time dimension that is not used by any fact table and/or create a view that adds dates as time goes by.|||

Instead of telling the cube (via the data source view) to read directly from the time dimension table, we tend to use db views that restrict the members to only those members that appear in the fact table. You can change the dsv to read from the view and you will then only get dates that appear in the fact table.

However, with the time dimension, you want to make sure you fill in all dates of a year -- if you don't you can't really do year-over-year comparisons reliably. So, perhaps you can have the view return all dates from your time dimension table that start AFTER the first date in your fact table. You can use similar logic to restrict the dates on the high end as well.

|||

So does this mean, for each fact table - you have a different view set up?

We have about 15-20 cubes that we want to set up which all use the time dimension, with varying volumes of historical data from each of the source systems. Is it best practise to then limit the data returned by the dimension for each group of cubes?

I was hoping that there would be a technique/feature that prevented 'redundant' (i.e. rows that do not have a related fact record) dimension members from appearing in the cube, that way we could begin to build the suite of cubes in a smaller number of projects and reduce administrative overheads (by having several Time Dim views for different date ranges for example)

Regards,

Tobias

|||

Well, our one view looks at all of our various fact tables to determine which entries are needed.

The problem you have is that Excel will list out all dimension members, so I'm not sure what the best solution is for you. But I don't think there is magic property that you can tell Excel to list only dimension members that are only for a particular cube.

|||Is it Excel 2007 you use? Because then you can create a named set that only contains dates that are non empty, has rows in the fact table connected to it. I′m not sure if Excel 2003 can use sets.|||We're using Excel 2003. shame there isn't a feature that does that.|||

Even in Excel 2003 pivot table, you have the option to hide the members that do not have data.

As you are using time dimension in different measure groups, the best practice would be to create hierarchy in the date dimension like Year ==> Month ==> Date levels that would make the navigation easier for the users. There are lot of articles on ways to handle it. Pl refer to BOL and newsgroups.