Louis Davidson
SQL Down Under Show 12 - Guest: Louis Davidson - Published: 2 Feb 2006
In this show SQL Server MVP Louis Davidson describes the top ten errors he sees being made in database design.
Details About Our Guest
Louis Davidson has been designing and implementing databases for 12 years, currently is a senior database administrator and architect for Compass Technology in Nashville Tennessee. And is a Microsoft SQL Server MVP. He's worked with PASS the Professional Association of SQL Server for the past four years with their special interest groups and has written and contributed to several books. Including the forthcoming Pro SQL Server 2005 database design, configuration from APress.
Show Notes And Links
Show Transcript
Greg Low: Introduces Show 12 with guest Louis Davidson
Guest this evening is Louis Davidson. Louis has been designing and implementing databases for 12 years, currently is a senior database administrator and architect for Compass Technology in Nashville Tennessee. And is a Microsoft SQL Server MVP. He's worked with PASS the Professional Association of SQL Server for the past four years with their special interest groups and has written and contributed to several books. Including the forthcoming Pro SQL Server 2005 database design, configuration from APress. So welcome Louis.
Louis Davidson: Thank you.
Greg Low: So what I might do first up again as usual is get you to just spend a few minutes to describe how you come to be involved with databases and in particular with SQL Server.
Louis Davidson: It was a long strange trip.
Greg Low: It is for most people I think.
Louis Davidson: About 12 years ago I was in college taking a database design class. Not doing very well truthfully but the professor had a job offer for me. Said does anyone want this job opportunity? I was the only guy that raised their hand.
Greg Low: Usually in classes people are so keen to do that.
Louis Davidson: Yeah the pay was pretty bad so. But it was better than what I was making. Flipping burgers. I was managing people flipping burgers which is even worse. But it was this prior to working on a Macintosh even and it was horrible. I took over some really bad work and made it really, really worse. On these really horrible wide tables and just, terrible, so I finally ended up leaving that job and got a job as a Milan administrator and putting the trips together in the National headquarters. And I was even worse at that. I had to serve these little ladies who know nothing about computers. Other than that they had the best computers because they were married to the people running the place. I don't have a tremendous amount of patience so I almost lost that job several times until we came up with this project where they wanted to get rid of this mainframe system that was costing them like $15000 a month which was a tremendous amount of money for us at the time. So we suggest that we downsize the product and use this product called SQL Server. So we built this product using SQL Server one point something and then ended up on four point two one which was incredible. We had fifty megabytes of disc space. And I remember writing hundreds of store procedures and triggers. The guy who I was working for was really into normalisation and doing things right. So he taught me a lot of really great practices. When I left that job I went to another job with another guy who was really into doing things right. So I've had this experience of learning through other people and the only thing I've really done since then professionally is SQL Server.
Greg Low: That's great. And so your current involvement with SQL Server is what sort of things? So, Compass Technology?
Louis Davidson: We do things like a lot of customer relationship management type work. We use the product CRM. We are building a data warehouse to test some products. Hopefully I can say I have recently designed a data warehouse for a system that one of clients uses. And so I stared getting into data warehousing. But so far all I've done is design relational databases and written the code for them. The same thing for a couple of years and I love it.
Greg Low: That's great. And I see you're very active in community things and in fact we'll talk about PASS a bit later but I must admit I also see you on news groups and things a lot of the time as well.
Louis Davidson: Yeah I love the news groups. You'll probably be able to tell by the end of this interview that I'm not the best speaker and it's not that I'm completely anti-social but news groups are great plus you interact with people and meet people and help people, but I have a back space key that I can... usually comes right down to it.
Greg Low: Well listen, the thing that we were going to talk about in this show is really your top ten design practices or issues or the things that you see people make the biggest mistakes in. So maybe if we start with number ten. What's your number ten?
Louis Davidson: My number ten is not listening to me. What I really mean by that is you have people, people in big companies tend to use people in strange ways. You gotta person who can design database he'll have them write scripts. You'll have a person who can design UI he'll have them building something else. And so that was really the gist, was getting the right people to do the right things. Because someone who's designing a user interface isn't going to think about how to design a database. Because why would they? They want their screen work as good as possible.
Greg Low: Actually, that really interests me as well because I notice with a lot of the new products that Microsoft's working on at the moment like the New Expressions Series and so on and products like Sparkle and so on. The sort of thing they're doing is getting someone who is a basically user interface designer and giving them tools to go and design the interface for the application and then say well ok we're gonna use that basically as the user interface and then we're gonna build everything behind that from that point on. Do you think that may tend to end up with designs that are sub-optimal? Might be a nice word.
Louis Davidson: Well this is dangerous ground for me because we're building with CRM right now and it does exactly that. The screen basically is like one entity equals one screen from what I can gather. I've been doing the warehouse parts for. And then it builds a table, set of tables and does all the work in the background and the database is, can be like you say sub-optimal.
Greg Low: I think the one that sort of troubles me is I know just with a lot of consulting work I've done, the customer I find the hardest to deal with are the ones who tell you how to do the thing rather than what they need and invariably they come up with a way of doing it that is not good. And because they just don't know what's possible.
Louis Davidson: Absolutely. You have someone who does, you hire someone to consult something or design something and tell them how to do it and like you say it ends up not exactly as it should be.
Greg Low: I had an old boss who used to describe it as going out and buying a dog and then standing at the gate and barking. That same sort of thing. So what's number nine then?
Louis Davidson: Leaving data integrity to external programmes. The problem there being that SQL Server does a great job protecting your data, uniqueness, foreign keys, even triggers. When you need to do something with, to protect the data, it has it all built in. Why use some external programme to do that when you then have to do it in 15 different places? Even things like building data loads from external clients. No one's gonna you know, you wanna use DTS to use them. It's great having that just built into the tables so it automatically happens rather than having to rely on some external code. It's not terrible if you're using only that one interface but you wanna be able to trust the data once it's in the database is correct. So if you have all your keys and all your constraints on and you have them trusted you checked them you have SQL Server check to make sure they are true there's never any question as to whether they are right or not.
Greg Low: Yeah in fact I think one of the discussions that always comes up with that, where to put constraints and how detailed are those constraints. I know when we were at the conference last year in Joe Celko's session he was arguing very strongly the very detailed constraints. He was very keen on them being at the database level, and where other people in the room would see them as being at some sort of business level. I think historically part of that being hard to change that at the database level. But that sort of thing now is pretty easy to change.
Loyuis Davidson: Well it's awkward to change in some ways and it's also the error messages you get. I'm all for the middle tier, the multi tier, whatever works best and scales best. But I wouldn't suggest, even if you're gonna check at the middle tier, I wouldn't suggest you not check it at the data level. Because like I said once you trust the data, once it's in the database and it's trusted to be true, no other process can accidentally mess up the data.
Greg Low: I think that’s one of the things too that at least if it's at the database level it doesn't really matter what the clients are that connects to the database. I suppose the difficulty is that if you do have a business level that's fine and that’s good but again if you're putting the constraints only at that layer you've really gotta enforce every single client that could potentially connect, never actually connects but through that business layer and that starts to get hard to do sometimes.
Louis Davidson: SQL is such a great interface to put data in tables in the database. Why wouldn't you use that? Sometimes. You're not gonna build an entire programme to load some data from a vendor, you're gonna use SQL.
Greg Low: That’s great. What about number eight then?
Louis Davidson: Poor naming standards. I know this can turn into a religious debate about should it be plural, should it be single. If you've ever seen me on the news groups, yes, I get involved in those. But that’s not the sin. The sin here is not naming it one way or another. It's doing it many ways. Doing, naming things funny. Not following a consistent pattern so your users can figure out what you're doing. You'll see people who use surrogate keys, name it table name ID. So you have the customer ID and then in another table they might have an account and call it AID. And then in another table you might have Company Identifier. And people can't follow those things. And that's just one small example. If you have three name columns and you name one of them NM and one Name and one First Name and F Name. And so you have to go continually hunt down what do they mean, what does this column mean? What is an A32O7 column? Somebody knew.
Greg Low: One of the things I find doing consulting work is I can't be really specific in enforcing any particular naming standard because in the end what I need to do is whenever I'm doing work at a site is to make sure the work that I do looks like it was done by somebody else at the site and fits in with what they're doing. So I can't be absolute with what you do. But even then try to make things as consistent as what is already in use at the site as possible.
Louis Davidson: Everyone’s favourite, prefixing the table. Every table is Table Customer. Table Invoice. Column Invoice Number. Things like that.
Greg Low: I must admit I really don't like seeing it in front of Table but Column horrifies me.
Louis Davidson: I'm sure I’ve just offended ten people at least but, sorry.
Greg Low: Everyone has their style. I must admit even amongst all of the discussions we've been having on our own email lists and things about what are appropriate standards I must admit I find few that would be supported of the idea of prefixing a table with table and so on. Although there's more support for prefixing of view of view. Because people say I like to know that it's a view I'm working with but the opposite end of that I'm sure, again picking on Joe Celko, I'm sure that he would argue that invariably the other way he would like to be able to interchangeably switch views and tables and something that might be a table one day might become a view another day and so one. So he really doesn't like seeing a different naming convention for those.
Louis Davidson: That’s what I try to do. It is a lot nicer to be able to not make the user care. The reason I didn't like the table is because the user sees that. And they shouldn’t have to go, when they see a list of tables that they’re trying to write a report on, they shouldn’t have to go is that a table? Oh, that's a table. They should just say customer, I need to look at the customer. Because this isn't like visual basic where you have these names that you can't contextually figure out what they are you're never gonna do select star from column. It's not even natural to do that. When you have a check box, you may not know what a check box is so having a Hungarian check box something name, that makes sense.
Greg Low: I must admit even there I used to use Hungarian prefixes very widely when doing VB6 many years ago. But I must admit I've now, in the dotnet world, I tend to use the more common naming conventions there, so I must admit after having done both after long periods I've got no desire to go back. I’m now very used to not use Hungarian. In fact, I find it kind of odd now. But yeah, certainly still what I have to do periodically VB6 work and you know, I do use it religiously like I used to. In fact one of the things that intrigues me with naming standards, the only other thing while we're in the getting things off your chest sort of thing, one of the ones that annoys me actually is there isn’t more prescriptive guidance from Microsoft. In fact I'd like to encourage them to do that. I see a lot of people on the group say it doesn't matter what you do as long as it's consistent. I think there's two problems with that. One is that consistently bad is still bad and the other one is that I think that’s a cop out. So if someone's just coming along saying what should I do? Saying to them do anything you like as long as it's consistent, I really do think there should be more prescriptive guidance coming forward.
Louis Davidson: I agree with you. In that respect. I mean, in my book I do, I got in a lot of trouble the first time I wrote the views, I gave too much information on how to name things. I always follow a certain pattern of naming that I found from the Idef1F standard. Although I've sort of added my own flavour over the years to it. I don't like the way they do some things but there is levels of goodness and levels of badness.
Greg Low: I certainly intend to have a show on sometime soon just on naming and I'm sure it'll turn into a religious debate, but I think it's one that will be good to have. So what’s number seven on your list?
Louis Davidson: One size fits all. This is kind of where you overuse things like property tables. In the attribute type tables or even XML these are great tools but some people take it just way too far. Instead of designing what they need they say oh they can have this table here. If we come up with something else we'll just put it in this table. Probably the one, one size fits all is the one domain table. Where you have this identifier you have the table name and then you have the value. I used to find that horrifying. Because it's so hard to join to, so hard to work with. People get this idea that if you have too many tables it's gonna be costly and harm performance. But in SQL server having more tables is not a bad thing. The more tables you have the better because you can index them better, people deal with them better.
Greg Low: Yeah you're saying compared to collapsing them all into a table, where you then end up with complex logic to pull things out of it.
Louis Davidson: Yes. The theory, one of the theories is you can have one editor that covers all the main tables. That's, you know, a pretty weak argument.
Greg Low: Well I think in the end you end up with one editor that covers everything but it's awful to use as well. Because what you're trying to do is much more complicated than it would be.
Louis Davidson: I’m a big fan of having domain tables for code names. You can have, some people like to have check and strength to cover domains. I like having domain tables as much as possible. Because if I find additional information about that domain I can add it.
Greg Low: Have you got a good example of what you're thinking of there?
Louis Davidson: Let's say a unit of measure. You might have a unit of measure that's an ounce, pound, etc. Well, you can have just a column, have OC and LB or whatever, for the ounces and pounds, but then you might want to add conversion factors, you might want to add alternate spellings. You know, this is the reporting value, this is the description of what it is. And if you put it all in one table it's very hard to do that additional information. And if you're not gonna be able to extend it, then I would say why not just use check and strength? To check for the value.
Greg Low: What about number six then?
Louis Davidson: Columns with numbers. This one always just screams poor normalisation. And a lack of understanding of relational programming.
Greg Low: You're meaning as in address one, address two, address three.
Louis Davidson: Not necessarily. Because there are a fixed number of these. There is a bit of that. You went right to the difficult example. You know the easy example is I've got a customer and they’ve got payments. I've got first payment, second payment, third payment. And I've got maybe one for each month. And what happens if they give another repayment? There's plenty of those examples. Or even sometimes, I put out this blog or this post, looking for bad practices so I wouldn't necessarily be talking about people I've worked with or for and one of these examples someone gave me was they had a table that had like complete rows with four, five columns named column one, column two, column three, and then they had it repeated for the second value. Like say you had address one, line one, address one line two, address one line three then you have address two line one, address two line two address two line three. So that’s the heinous thing because then someone will invariably say can I have another one? Well you can but it’s gonna take about six years programming because you've gotta test, you've gotta do this, you've gotta roll it out. You are right. There are some cases where it's not a problem and what we're always gonna do, the address line one address line two. I've actually, I've built a table where it's an address and it has a sub table to hold the address lines and I’ve also done the address where you break it down to every possible piece where you don't actually need address line one address line two.
Greg Low: I did pick a complicated one there. And I think part of the reason I picked that is it's one I regularly see both ways. So yeah.
Louis Davidson: I've actually found if you're gonna do that, just have an address column. There’s no need to have an address line one address line two. You have address with a cariger term there or an XML. Having two fields makes it a problem.
Greg Low: Do you think this is related though to the fact that SQL server doesn't have a raise as columns? Maybe in other databases, we could have an address column and we could say it's vacha 35 by 4.
Louis Davidson: I never thought that would be that much better than having a related row is it?
Greg Low: I think it depends on the situation. For every situation where I see somebody go address line one address line two address line three address line four. I just wish that was address line array of four personally. And I must admit having worked with databases like Progress and a number of others that have array column types it seems very, very natural in those sort of environments. But it's one I've certainly learned to live without any particular problem.
Louis Davidson: One of the rules that has a single column that says address and you put all the data in there. One of the rules of thumb I've tried to force myself into is if I'm not gonna ever deal with data inside SQL, I don't break it down into multiple columns. In that case having the return is fine because I'm never gonna write a statement saying give me this part of the address. If I am then you normalise it more. You make a street number column, you make a street name column you make a, all the thousands of little pieces.
Greg Low: That’s right, a suburb, a state, all those sort of things.
Louis Davidson: Even a sub-type.
Greg Low: That's great. Well that's probably a good point to take a short break and when we come back we'll look at your top five.
(Break)
Greg Low: Welcome back from the break. Before we get into your top five Louis we'll just get you to, anything you want to share, tell us about where you live and what your hobbies are and anything like that.
Louyis Davidson: Well I've lived quite an exciting life. I have three main things in my life. I have church and school, entertainment and gadgets and databases. When I say church and school, my wife is a principal at a private Christian school. I'm also a Deacon at this church. My daughter plays volleyball, is always in plays, she's a score keeper for the basketball team, so I spend a lot of time there. At the school and stuff. I'm a gadget head. I love music and TV, old, new TV, American, English, in fact I have memories of the old Paul Hogan show. I'll have to find that on DVD some day because that was a great show.
Greg Low: I was gonna say, so what’s your latest gadget?
Louis Davidson: My pocket PC phone. I love this thing because I can sit there and work anywhere I'm at. I read blogs, I write stuff, play games, listen to music. It's an audio box. Windows mobile-based.
Greg Low: I was listening to the tablet PC podcast just in the last few days and they were talking about the consumer electronic show, the device that seemed to get a lot of attention, the new little jewel called, CPC which is a machine that runs both tablet PC edition and alternately can run Windows Mobile five and has a shared folder, can copy between, it's got a 40GB drive and a bright little, I think it's 800 by 600 screen. A little device but quite an amazing thing. I think there's some interesting new devices coming.
Louis Davidson: I'm always usually one generation behind at least. Enormous amounts for the current version. I bought a palms smart phone when they were just about to go and move onto the next version. I type on it, I write on it. It's a great toy as well as a great tool to get work done. Because then when I'm sitting at a basketball game and it's boring, I whip it out. I find myself working sometimes when I really shouldn't be. I should be listening to someone talking. It's my job right but it's also my hobby.
Greg Low: I think for most of the MVPs, it would have to be. The amount of time they spend on it and community things and so on it would be hard to imagine that not being just, it has to be a passion for them.
Louis Davidson: I hear a lot of these people talking and they do more than I do. And I feel like I'm always doing this stuff. And they're like I'm travelling here, travelling there, talking here, I'm like, wow.
Greg Low: Well so listen, we might then head onto your top five. So what's number five, working our way down?
Louis Davidson: Designing as you go. It’s sort of to me, probably the most obvious but it's so common. The funny thing is in most industries you never do this. If you're gonna build a house you don't call a contractor and say I want to build a house. They start pouring the foundation and there's this other guy who's sitting there with what kind of style would you like? You sit down and you draw the plans. You know what size you want the house. They build and say is this what you want? And yes. You build it. And you know they tweak it a little here and there because not everything's right when you first do it. But you don’t start hacking at it. And I don't believe you should design everything and have this perfect design and never expect to change it. I understand the waterfall thing was never perfect but it's like too many people have taken this so far that they're not thinking ahead. So you get to this point where you’re just trying to design something and you're like oh no I forgot that and I've gotta redesign that. And you say well I could redesign that or I could just slap something else in there.
Greg Low: Really the problem that occurred there is where you say hey I could redesign it properly but I won't because it's just, is that because it's just too hard to change?
Louis Davidson: It's kinda like I had this problem myself in that once I've done something I don't wanna change it. If I'd built this screen that's all pretty and I'd built the report already and it worked really nice I wanna make a change to that I've gotta change all that work. When I could probably get away with not doing that you're gonna try to do it a different way. Just because you didn’t think ahead. And it's hard. This is the kinda thing, it sounds like I'm blaming people. But I'm not. I know there's always a manager who's working for a person who sold something to someone. They said we'll have this done. And database design is not the most sexy of the things in the computer science. Now, you build a database; it's boring looking. Even the model, no matter how pretty the model you build, no one's gonna be like whoa look at that model. I wanna have a model version of that model. So we get stuck and then they get these pretty screens and you've gotta build the database to fit it.
Greg Low: That leads to your next one if I recall which was tying UI design to the database structure.
Louis Davidson: And you could say that vice versa, tying database structure to UI design. User interfaces are really important. I'm not saying that user interfaces should be built to look like a really normalised data set. They shouldn’t be necessarily. Because you may end up with twenty tables that represent that in one screen. That's fine. So that's what store procedures are for. That's what coding is for, working together and building that middle tier. What architecture is for is to be able to present data in a way that's both usable to the client and usable to the database reporting tools and things. So you put in, like, the modification anomalies, duplicate data, out of sync data, stuff that is very costly to fix.
Greg Low: So listen, one of the things that intrigues me with that though again, whenever I hear these discussions it always comes back to it's expensive to fix, it's hard to fix, all those sort of words and what I keep struggling with is, I keep wondering, if I look at what’s occurred with the dotnet programming languages and so on the refactoring tools and things that are now coming with them, are there to make it really easy to look at something and say hang on, yeah I know I did it that way but I really now want to fix that, but the tools are helping go through and fix all the consequences of the fact that you're then changing that. And one of the things I still find in the database area is we just don't have that sort of rich tools sets. I don't think then tool sets have kept up at all to the point where if I say that hey I just want to make this change to how things are done it's a lot harder to do that with the standard tool sets.
Louis Davidson: I hope you don't want me to disagree there. Our tool sets or SQL programming, I don't want to bite the hand that feeds me Greg. I already did that earlier. The people that actually give me cheques. I don't want to bite the other people that support me and give me stuff but the tools, they're getting there but you’re right. It really goes back to what I was talking about, about thinking ahead. The structures are not as malleable as a user interface or really any other programming I think. But there's one good thing, that if you design it, if you've normalised it and you've represented each kind of thing with a table there's not a lot of changes there that's gonna occur. You're not gonna find out oh that really wasn't a hub checked. That really wasn't an entity in my database. You're more gonna find pieces and parts that you need to add to there. Or you might find that you need to break up tables. But you generally don't find that you have too many tables.
Greg Low: No I think the sort of thing I was thinking of was imagine we say I've got a product and it's got like a product category for example and that's a foreign key relationship to some product category table and all of a sudden people say oh actually we've now decided products can be in more than one category. And, so what I really now want is to remove that column, I want to build a linking table between the two and so on and so on and I'm just thinking that whenever I see that suggested to someone with a database they kind of cringe a bit because they go... right. And I think it's because they’re thinking of the consequences of what's gonna be involved in that but I'm just thinking that simple refactoring like that, it would be just nice if there was another layer of tools that made that sort of thing easier,
Louis Davidson: I agree. I want that. You're right, I mean, even Irwin would it’s a lot. It has some tools that do that but I don't use that, I generally do a manual process.
Greg Low: Yeah, it's just one sort of refactoring that occurs to me but I just think there are many, many, many of those sort of things where we could describe standard things that we just endlessly end up changing. And there was nothing wrong with the original design it's just maybe the business need is changing or something like that. I think when I look at the agile guys in the developer community, are the agile end of town, they deal more with the fact they’re saying it’s too hard to get the design right in the first place and what’s important is flexibility while you're building it and being able to adapt it. And there are clients I've worked with that no matter how much effort you put into the design the people you’re working with are not… I had one boss design it as spatially aware. Or something, but basically, some people just can't see in their heads what the thing would be like or what the things are until they actually see the thing. It's, I’ve got a partner Wendy, she was talking about a guy, she was talking about the fact they were talking about remodelling a house. She could have imagined what it would have looked like, but he couldn't manage to do that until he actually saw it. And I think that’s part of the problem, you've got clients you're trying to deal with and if you're trying to get them to dell out in incredible detail what the thing needs to be, sometimes that's really tough. Because they just can't do it.
Louis Davidson: The thing is you just have to learn to ask the right questions and you have to listen. I find that most people that you have to do these things with; they know what they want as long as you know kind of how to ask the questions. I find most times I get in trouble are when I guess and I jump to conclusions and I tell them what they want and then they go well it must be right. He's the database guy.
Greg Low: I think more what I was thinking about is I remember a client a little while back I had one and the sort of thing you could say to them is maybe some reference number or something you'd say they have a concept of account and they have a buying group the accounts live in and you say look is it the same for every single member of the buying group and they would look at you and say yes, yes, yes it is. And then eventually when you go to input the data you find that it's different for one of the accounts and you say we talked about this and you said it was, oh yeah except K-Mart. Right. But the thing is at the time it just did not dawn on them and you've actually asked the right question. They've thought about it, they've spelled out the detail but in the detail they were wrong. And I don’t know any easy way to deal with that sort of thing. And I think again tool support is what I suspect is needed.
Louis Davidson: Yeah I know what you mean and you’re right.
Greg Low: But normalisation is kind of interesting and in fact that leads into your third point.
Louis Davidson: Oh yeah, regarding some poorly designed tables because they're normalised. My design isn't bad it's just normalised and it’s funny how people use that as an excuse.
Greg Low: What do you see as valid reasons for de-normalising.
Louis Davidson: Performance. If you build a proper interface between your tables and the user interface there's no need for normalisation except for performance. Because and de-normalisation you can do in a store procedure or a view. And the user needn't know that you’ve even done that so you might need to de-normalise for performance. Maybe if you have a column that's calculated, it's used like a thousand times a minute, and you only would need to calculate it once per month or something. Sure you might want to keep around that sort of data. Probably in some sort of index view or something, whatever the best mechanism to get it done without having to do it and redo it over each time. But if you built the interface right people won't know that you’re normalising and so the whole interface, the whole inserts, and updates, and deletes and edits should be done on a set; that the user wants, that user interface wants. The way you store the data is normalised so even if you present the user with data in format X you store it in a normalised format so you don't end up with a problem with having summary data having multiple things related to the same thing.
Greg Low: The main reasons I find it's necessary are very much performance things. Probably the first one is the idea of having very, very wide tables that 99% of the time you pull two columns out of things like that. But again you could probably get around that with indexes, again, build an index that just contains those columns and some sort of covering index.
(Break – Specialist Interviews)
Greg Low: The other one that's really to do with locking contention, that's where I always start to get nervous, if there's any sort of transactional value stored in the same table as property-type data, you know, so names and addresses and you know, customer's details. I start to get nervous when I see things like last sale dates or any of those sorts of things in that same table, and probably mostly because I get nervous about it in terms of locking. Transactional stuff tends to get locked regularly and I just don't like seeing those sort of generic property tables and things locked much at all.
Louis Davidson: Very true. It's kind of the thing I was saying you could put that in view and have the same data available without the user actually knowing.
Greg Lo: And what about your number two then?
Louis Davidson: Design based on an old system. This of course are things like, we see it all the time with products that we buy. You’ll get a product and you'll go into the database because you want to get some information out. And what it turns out it was an old D base programme. And they're using it exactly like they were back fifteen, twenty years ago in the same format. I have a perfect example that I won't share the name of, but they actually take some of their tables and store it into the database as tables so when the user needs it they'll pull out the table, materialise it as a file on the desktop and use it. And then put it back in when they're done with it.
Greg Low: Actually on a similar one I must admit I find as people move forward using old data systems and move forward they often don't take advantage of things that you can do with the newer systems.
Louis Davidson: You mean like with the new features of 2005 or that sort of thing?
Greg Low: Yeah could be one but I mean even simple things like if you've come from an old system that could only have numbers to look things up, for example, and then they keep them instead of going to something maybe alphabetical that would work and be much easier to use. But because their coding and everything was done back twenty years ago they're not game to change anything like that so everybody ends up using an ugly system even though a new system could do something different.
Louis Davidson: That's actually better than the one I was thinking. The one I was thinking, because, I can understand why they don't use some of the newer features because some of these people are supporting, you know, SQL server 4.2 in some of their products. Because it still works, why upgrade? Or so I've heard. I dunno. I like to upgrade the second.
Greg Low: No, that's very much; it is very much the case actually. When I used to work as an ISV, the last thing I wanted was newer versions all the time. In fact we used to work with Progress years ago and, for example, if I built an application that ran on Progress 6.2 you know, l something or rather, whenever a customer bought a customer license to run my application, I actually wanted them to have 6.2l whatever. I didn’t want them to have 6.2 N or M or O you know, because I hadn't tested on that. See I think if they're running a specific application and the application hasn't changed and that’s the pony app they run then all you can do by changing is potentially break it.
Louis Davidson: Yeah but a new client has to have a server running this version and some of those earlier versions didn't support multiple instances. A server in this pack and a server in that pack. And every, you should need but one database server run as many packages as you can handle hardware wise.
Greg Low: I think where the hassle comes in is that where lots of little releases come out on a regular basis, I don't find it too scary with SQL sever because you're only talking about occasional service packs or things like potentially maybe a hot fix or something like that occasionally. But where I find it really awkward is things more in the front end side. For example I had one a while back where there was a new security update that Microsoft incorporated had sent down Windows update and yet that, what it means is that every single site breaks first thing that morning. And that's pretty scary for an ISV.
Louis Davidson: Yeah, that is true.
Greg Low: I think that's the stuff that my nightmares are made of.
Louis Davidson: The question there is what do you do about that? I mean, tell them, don't open the packages until we test them? I've seen that. And it really hurts your patching; they don't ever get it done within a reasonable amount of time. Or what seems like a reasonable amount of time. Which is immediate. You don't want to wait.
Greg Low: See that's the problem I see is that when everybody finds the problem first thing in the morning they all want it fixed by 10 o'clock too. No matter what it is.
Louis Davidson: I agree with that.
Greg Low: I think also what makes it hard sometimes in that particular case is it took many, many hours to work out what had changed because what had happened, the symptom you see, is not all that directly related to the name of the knowledge-based article, for example. So yeah, that's the trouble. I think the whole engineering thing is with engineering as a discipline I think you don't tend to change the underlying things without testing. That makes me nervous.
Louis Davidson: It's amazing how complex Windows is. Or any of the operating systems because back in mainframe times there were, when I was working, when I was in college we had a mainframe and they said at anytime there was like 10,000 bugs in system360... In fact that operating system was so simple compared to Windows it's not even fine, so...
Greg Low: They used to say that in any piece of software big enough to be useful is never bug free.
Louis Davidson: It's true. You see how long it takes to get it the closer to perfect to get, it takes longer and longer and longer. And that doesn’t' take it for people’s perception.
Greg Low: So what about your number one issue then? What's at number one issue?
Louis Davidson: Improper key structure. The key here is; I’m a big fan of surrogate keys. I use a surrogate key based on an identity on almost every table I build. But so often you see people use identity keys, they don't put any other keys on the tables and then you end up with duplicates, bad performance for searching.
Greg Low: In your case you're talking about something like a record ID effectively which is an identity column and you have that on every single table and that is the primary key? For example if you had a customer table you would have still a record ID or whatever is the primary key that may have a customer code or something as another key?
Louis Davidson: Yes. I would always have some user key but I don't think you should ever use the surrogate key as value for the user. Then the user won't, gonna have to update it. I don't want that to be key number one I want it to be key number 6. Can update it. So, a lot of people suggest it, it's best to have a very thin key, very key for your cross training key and such so I like that structure and I also like that every table follows a set, going to be this single value that UI use to go out and get a row in my programmes so I can write my triggers, I can write my store procedure codes that all knows that fact.
Greg Low: And it knows what the column’s called too.
Louis Davidson: Yes. The key is you have to have some real value that's unique.
Greg Low: The question I’ve got there is which do you then use for foreign keys in your case?
Louis Davidson: Always the surrogate key. Identity value. Again because it's so small it's very fast and it works really nice because the name works out perfectly. You know if you see the customer ID in there you know that represents the customer. It's pretty self describing. I don't suggest you have users working with that, a hand, but when they have to do joins, obviously it's easier for them to join on customer key, customer ID they know that’s part of the customer table and you’ve joined to the customer table.
Greg Low: So that's your number one. Actually that's interestng you mention that because I had Graeme Simsion on the show a few weeks back and that was, I think Graeme's number one design modelling issue, was improper choice of primary key. Bizarre. His feeling on that as well. So it's clearly a common problem. I also remember Wally Mc Lure, an ASP Nate MVP and I remember Wally when we were at a software design review at Microsoft a year or so back and Wally what was saying was one of the things he struggles with are clients sights, he invariably gets there and there are no primary keys at all on tables and so on and it makes his life fairly awkward when he starts working with it so it clearly is a very common sort of design issue.
Louis Davidson: That goes back to the leaving the data integrity to external programmes. You put a key, you just expect that somebody's taking care of that but it never seems to be perfect.
Greg Low: Listen, that brings us pretty much up to time I'm just sort of interested in what else is sort of happening in your world. You’ve got the book coming out soon.
Louis Davidson: Hopefully in the end of March.
Greg Low: And so that's called Pro SQL Server 2005 Database Design and Configuration. Oh and Optimisation.
Louis Davidson: Some stuff with coding. Some stuff with the nexus. The way to optimise your design; not so much optimise the system once you've already built it. I love that title. It's the longest title possible. I think it's a record. That's the main thing I have going on. That’s been like the thing I’ve been doing for the passed year. Hopefully go back to Tech-Ed this year and spend some time with the Kabanas. That was a lot of fun.
Greg Low: And we might see you at the PASS conference in Seattle later in the year?
Louis Davidson: Definitely have to be at PASS. And then I plan to spend the whole time playing my game cube because I’ve had things sitting around just waiting for me to do them since last year. I started the book last January. So if you’re thinking of writing a book out there it takes a long time, a lot of work.
Greg Low: Yeah I think people underestimate the time involved and I think the other thing they underestimate or overestimate is the dollars that’ll come back. I know that what most people work out is they would be better flipping burgers somewhere than write a book so.
Louis Davidson: I was just about to say that. You're not going to get rich. It's fun and I'll tell you what it does, it makes you learn a topic well. Because the first time you get bashed by a technical reviewer who says you’re stupid you don't know that, that's terrible, how did you do that? You go, oh no. You realise you’ve gotta learn this thing to death. It's just like speaking which I don't do a whole lot of. Well hopefully like I said you're editing out some of the good bits here but, it's not like speaking where you gotta do it once. It takes time and you do it over and over.
Greg Low: Well listen, thank you Louis, it's been really great to talk to you and we'll talk to you again soon.
Louis Davidson: Take care.
Phone: 1300 SQL SQL (1300 775 775) l International +61 1300 775 775 l Fax: +61 3 8676-4913
Copyright 2017 by SQL Down Under | Terms Of Use | Privacy Statement