Good practice to separate tables with static data?

Question

Good practice to separate tables with static data?

amns

SSCrazy

Points: 2628
More actions
July 1, 2014 at 10:49 am

#290530

Hi!
I have a database with several tables, some of them contain static data, meaning rows that are added to the tables when the database
is created.
I would like to separate these tables by their names for easily see which tables contains static data and to favour deployment scripts.
Are there good practices for that?
I'm thinking in two options:
1 - rename these tables to Static_[table_name]
2 - create a different schema with name 'static' for these tables
Each one will require update of all dependent objects.
What would be a good choice?
Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

amns (7/1/2014)
Hi!
I have a database with several tables, some of them contain static data, meaning rows that are added to the tables when the database
is created.
I would like to separate these tables by their names for easily see which tables contains static data and to favour deployment scripts.
Are there good practices for that?
I'm thinking in two options:
1 - rename these tables to Static_[table_name]
2 - create a different schema with name 'static' for these tables
Each one will require update of all dependent objects.
What would be a good choice?
Thanks!

Go with option 2. I would MUCH rather have a schema setup for this type of thing. Naming objects by their usage a bit obtuse to me. Also it would be a bit easier to deal with permissions. Presumably you don't want people to update etc on your static data. DENY those permissions on the schema to user(s) that shouldn't be doing anything to those tables.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 2

I'll also suggest putting the tables in a different file/filegroup and, possibly, making the file group READ only depending on whether or not the tables are trully static. For example, an audit table is largely static for legacy data but not for current data and that should be handled differently, although a separate filegroup works well for those as well (just not necessarily read only).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

amns SSCrazy Points: 2628 More actions · Answer 3

Thanks for your suggestions!

I think i will go with the distinct schema for the static data.

Best Regards!

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Just piling on a bit so you have some reinforcement. I'm with Sean and Jeff. Create the tables, but put them in a different schema. Separating function by naming convention can be pretty problematic at times and it can reduce clarity. The one thing you want to try to maximize with naming objects is clarity.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

amns SSCrazy Points: 2628 More actions · Answer 5

amns

SSCrazy

Points: 2628

July 2, 2014 at 7:05 am

#1726462

Grant,

Many thanks for your opinion!

ScottPletcher SSC Guru Points: 101092 More actions · Answer 6

I wouldn't want to put them in a separate schema unless you really had to. Aside from hideous renaming issues (*), you might end up with what seems like odd security issues when the different schema names break "ownership chaining".

You need separate documentation of every table that gives details about the table. Being a DBA, I use tables to store the documentation as well 🙂 .

For now, just create a single table called, say, "documentation", and put the appropriate comments and flags in there. The table def will need fleshed out more, but here is a very minimal start:

CREATE TABLE dbo.documentation ( schema_name sysname, object_name sysname, is_static bit, description varchar(1000)

, notes varchar(8000) --optional

, extended_notes nvarchar(max) --optional

, ... )

(*) Edit: For example, a table that previously had static data no longer does: do you change its schema to 'dbo'? Or a table that didn't have static data now has some: do you change its schema to 'static'? Putting that type of meaning into a name is a very bad idea IMO.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

amns SSCrazy Points: 2628 More actions · Answer 7

It is a good point also!

In my case the tables with static data will mostly not change.

I have been using the extended properties to document the tables, but the table with the documentation

also seems a good idea.

Best regards!

ScottPletcher SSC Guru Points: 101092 More actions · Answer 8

I also make the doc tables "live". That is, the code that created/initialized/set up a new db should use the "documentation" table to determine whether the table is static or not, and whether it needs loaded. Then the documentation can always be trusted, since it's actually being used.

Similarly, I have a dbs table of my own that has, among other things, backup details for each db. When the backup runs, it uses the data in that table to control the process. Thus, the table provides both documentation and an easy way to control backups.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 9

ScottPletcher (7/2/2014)
I wouldn't want to put them in a separate schema unless you really had to. Aside from hideous renaming issues (*), you might end up with what seems like odd security issues when the different schema names break "ownership chaining".
You need separate documentation of every table that gives details about the table. Being a DBA, I use tables to store the documentation as well 🙂 .
For now, just create a single table called, say, "documentation", and put the appropriate comments and flags in there. The table def will need fleshed out more, but here is a very minimal start:
CREATE TABLE dbo.documentation ( schema_name sysname, object_name sysname, is_static bit, description varchar(1000)
, notes varchar(8000) --optional
, extended_notes nvarchar(max) --optional
, ... )
(*) Edit: For example, a table that previous had static data no longer does: do you change its schema to 'dbo'? Or a table that didn't have static data now has some: do you change its schema to 'static'? Putting that type of meaning into a name is a very bad idea IMO.

I agree with Scott on this one. Separate schemas are a chore for this.

I also document tables at both the table and column level but I do that with Extended Properties.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)