I know this post has the potential for religious-level debate but it's time to make it anyway.
The more I've been working with Analysis Services lately, the more it grates on me that the BI community still seem to be the last ones hanging onto table prefixes. They're not doing "tblSomeTable" but they are using "dim", "fact", etc.
Hasn't the time for this long gone now?
Most of the argument seems to be about finding tables in a list of tables. You could do that via schemas if you really wanted to. But as Adam Machanic pointed out recently, from 2005 onwards many-to-many dimensions blur these lines anyway.
Is it time for the prefixes to go?
sorry mate, you will get many arguments on this. I am old school, and I love the prefixes. Granted they add to code and are therefore contributing to interrogation turn arounds in large schemas, as well as the various stalwart arguments all of which I would gladly concede. BUT I AM TOO USED TO THEM. I love knowing that I am working with something raw, as is told to me by those little "tbl" letters without needing to interrogate a schema which may not be available to me at my level of access. It aids data mining validity in knowing whether the data is a construction or a raw source immediately. Nah I like the prefixes… so goes the religious 1st blow. however, if you can show me a way that I can know just from looking at this source that I am dealing with a table without schema interrogation, I am all ears.
Dude I think that you're crazy also.
Table Prefixes are here to stay.
Our SQL Server tool set should _SUPPORT_ these table prefixes.
Right now; if we want to filter for a particular prefix; it takes _WAY_ too long.
MS should stop trying to preach to us– and start giving our tools the flexibility to support
tblTable
vwView
spSproc (I don't really care for spSproc, we usually have so many sprocs we've got 100 different prefixes).
Maybe it's cuz i'm an Access guy– tbl, qry, frm, rpt, etc.
but I'll never give it up.
I just think that BIDS should support massive renaming of columns; etc.
Hi Guys,
Sorry but look around. The debate on prefixes for standard relational tables is over. The only people that support them tend to be folk that learned databases from Access where they were used as a default for a long time, and have it as a design habit.
It also causes problems with refactoring databases. For example, if you need to insert a view to replace a table, you'd end up with views called tblSomething. I've seen this and it's ugly.
Take a look at SQL Server itself. They had "sp" for system stored procs and "xp" for extended stored procs. They now have both "sp" and "xp" for both system and extended stored procs as they've done maintenance. That's way more confusing than if they'd never had a prefix at all.
And Aaron, prefixing stored procedures with sp isn't advisable at all. It causes cache misses as SQL Server tries to look for it in the master database first.
Regards,
Greg
I don't use prefixes. Amongst other reasons, one of them is – sometimes my Customer dimension table could be also fact table.
But in Microsoft Adventure Works DW database (2005 and 2008 versions) all tables are prefixed. So many users will see this as "Microsoft recommends".
I posted a poll about this preference on my website. To my big big surprise during first hour 3 people voted that they are using prefixes.
Ok – now I am confused mate… replace a table with a view??? tables are supposed to contain raw data… and yes… I am an access guy.. surprise surprise… I guess if I know the schema, then I would know if I had a table or a view… but in most cases I dont and I know that when working in data mining excercises it is important to classify raw data as opposed to constructions. I have always found that prefixes are a true representation. I have never come across any view or SP with the tbl prefix – but I guess there is always a first time.
Hi Neil,
If you live in a world where you have one application working with one database, you also live in a world where if you need to modify tables, you can also likely modify the app at the same time. Larger systems just aren't like that.
As a simple example, it's very common when refactoring databases to replace a table with a view so that you don't break existing apps while you make underlying changes and leave it there until the apps eventually get modified. This sort of maintenance work becomes horrible if you have prefixes on the tables.
Regards,
Greg
ahh… I see… so its the old bandaid on the app patch with underlying festering infections… but the bandaid was expedient particularly in mission critical circumstances. Yes you get that in small apps…
This I feel is a more worthy topic than the prefixes. Leaving a view in the same place with the same fields as the original table so as to not break the backwards compatability… damn thats a bad practice, despite that it works and is expedient.
Has anyone ever come up with a better accepted method? Just because something is widely used doesn't make it right. I do program in an ideal world where one app = one datafile, but I have also had to work where 1 app = 15 different data files all collecting similar information by multiple people. Further the power to add to the collection was with the users… Try walking into that nightmare…
I understand that a mod to a large system has many underlying implications… lets visualise however the changes in tools we have, and the changes to environments… and see if we can figure that out…
BTW… I always have some sort of data versioning in my applications large and small… a quick check of the version will tell me if my software version works with my data version… and if not where to get my upgrade… I also tell anyone using my data with this method of my design change. I expect them to mod up/down appropriately. If their app breaks because of my design change, they have the option of staying where they are and using the older version until they can stream in.
In most circumstances, the cost of the forced change is much less than the cost of the temp fix – over a prolonged period.
My previous post is off topic… I dont mind if you scrub it… I guess my message is that refactoring is a very horrible area, and should be easier given our more modern tools and environment. It is also a much more interesting subject – as everyone has difficulty with it, and I absolutely have no idea why I am so passionate about prefixes, I frankly dont care provided i can identify raw data from constructs who cares what things are called.
hi guys
you know that a syllable or syllables added at the beginning of the root is the the part of the word called prefix
Hi Greg, just wondering if there has been any progress/consensus in this area in the last couple of years?
The reason why I bring this up is because this topic is still being discussed at my workplace so I think it's still somehow relevant.
One of the compelling reasons I can see for having the dim and fact prefix is that Management Studio does not provide a visual separation between them [when viewing your datamarts in sql server]. This is in contrast to objects such as views, tables, stored procs etc that are categorised into their own nodes in the tree structure.
Cheers