0

I am relatively new to coding in the Microsoft stack and some practices in my new workplace differ from things I've seen before. Namely, I have seen a practice where Read-Only tables (ones that the application is not meant to be able to insert/edit/delete in) are prefixed with "lkp.EmailType", "lkp.Gender", "lkp.Prefix" and so on.

However, when I started developing some MVC5 apps using Entity Framework and a Database-First approach - when debugging my code I noticed it attempts to both pluralize the table name and change the schema - so "lkp.Gender" queries take on a select statement on "dbo.Genders". After looking into the pluralizing functionality, it seems best practice leans toward pluralizing table names, so I went ahead and did that for this application (this is a new application but we are using a similar DB structure as prior ones but do not have to keep it the same).

The last thing I need to do - is change these table schemas to be "dbo" as opposed to "lkp". In talking with some coworkers on their other projects, they found while read only lookup tables might use the DBO schema for their project, they might name it differently such as "dbo.LkpGenders" or the like.

This takes a bit of work to remove constraints on other tables using these LKP tables and such and I wanted to ask the community before I put too much effort toward this change if it is even a good idea or not and put my time towards either making LKP tables work or doing away with them.

In short - Is usage of LKP schemas for read-only tables an old practice or is this still a good idea to do and I just have been in other workplaces and project who were doing it "wrong"? As an added bonus, reasoning why MVC5/EF may be using DBO schemas on something it created an EDMX fine out of would be good to know. Should I be using a naming convention, DB Views, or LKP schemas for this kind of read-only lookup data?

aohm1989
  • 401
  • 1
  • 8
  • 19
  • Altering my searches some, I did find some discussion on this topic - though I still would like to hear from other experts on this subject. http://www.sqlservercentral.com/Forums/Topic1664238-373-1.aspx It seems the consensus here came to "What issues does it solve?" in having an LKP schema, and to just do away with the practice. – aohm1989 Dec 28 '15 at 13:59
  • I don't currently use this convention but think it could potentially be quite useful. I normally have lookup data in post deployment scripts in SSDT and source control. Using this convention and publicising it internally could make it quite clear that the data should not be manually edited in the table itself to avoid changes being overwritten at the next publish. – Martin Smith Dec 28 '15 at 15:27
  • A set of read only tables will have a different backup and reorg schedule (or no backup or reorg schedule) than a set of updated tables. That's one reason to put the read only tables in a separate schema / database. – Gilbert Le Blanc Dec 28 '15 at 17:03
  • 1
    There's two issues here: best practice from a DB standpoint, and best practice from an EF standpoint. From the database side, this *is* probably best practice, but EF will not be able to utilize tables like this, so what is and is not best practice becomes moot. However, there are ways to make EF treat an entity as read only, even if it isn't enforced on a database level: http://stackoverflow.com/questions/10437058/how-to-make-entity-framework-data-context-readonly – Chris Pratt Dec 28 '15 at 17:36

1 Answers1

1

Some thoughts:

I like plural table names. A row can contain an entity; a table can contain many entities. But, naming conventions should be guidelines rather than carved-in-stone rules. It is impossible that any one rule would be the best alternative under all situations. So allow some flexibility.

My only exception to that last caveat is to name tables and views identically. That is, the database object Employees could be either a table or view. The apps that use it wouldn't know which one it is (or care) and the DB developers could quickly find out (if it was pertinent). There is absolutely no reason to differentiate between tables and views by name and many good reasons to abstract tables and views to just "data sources".

The practice of keeping significant tables in their own database/schema is a valid one. There is something about these tables (read-only) that group them together organizationally, so it can make sense to group them together physically. The problem can be when there are other such attributes: read-only employee data, read-only financial data, etc. If employee and financial data are also segregated into their own database/schema, which is the more significant attribute that would determine where they are located: read-only or employee/financial?

In your particular instance, I would not think that "read-only" is significant enough to rate segregation. Firstly, read-only is not a universal constraint -- someone must be able to maintain the data. So it is "read-only here, writable there". Secondly, just about any grouping of data can have some of that data that is generally read-only. Does it make sense to gather read-only data that is of use only to application X and read-only data that is of use only to application Y in the same place just because they are both read-only? And suppose application X now needs to see (read-only, of course) some of application Y's data to implement a new feature? Would that data be subject to relocation to the read-only database?

A better alternative would be to place X-only data in its own location, Y-only data in its own location and so forth. Company-wide data would go in dbo. Each location could have different requirements for the same common data -- read-only for some, writable for others. These differing requirements could be implemented by local views. A do nothing "instead of" trigger on the view would render it completely read only, but a view with working triggers would make it indistinguishable from the underlying table(s). Each application would have its own view in its own space with triggers as appropriate. So each sees the same data but only one can manipulate that data.

Another advantage to accessing common (dbo) data or shared data from another location through local views is that each application, even though they are looking at the same data, may want the data in different formats and/or different field names. Views allow you to provide the data to each application exactly the way that application wants to see it.

This can also greatly improve the maintainability of your physical data. If a table needs to be normalized or denormalized or a field renamed, added or dropped entirely, go ahead and do it. Just rewrite the views to minimize if not completely eliminate the differences that make it back to the apps. Application code may not have to be changed at all. How's that for cool?

TommCatt
  • 5,498
  • 1
  • 13
  • 20