DB design and performance

Question

I'm currently in the process of designing a database. We have an existing system that was not designed very well and we are ironing out all of the small issues that are clearly visible, e.g. a varchar for a true/false flag instead of a bit.

What I want to know is, how do you know that you have got a sweet database design? Or is this a myth? I mean, the structure may look amazing on paper, but when some data is in there how will it perform.

Are tables that store "lookup" values faster than storing full descriptive text? e.g.

Error table

Id    ErrorId    DateCreated
1     1          09/12/2011
2     5          10/12/2011

Error Description table

Id    Description
1     Warning - failed to validate
2     Failed to locate file

In this scenario, would creating a view be more beneficial than writing the SQL including the necessary join?

Sorry if I have posted this question in the wrong place.

Have you actually tried this? – TOP KEK Apr 11 '12 at 08:12 — TOP KEK, Apr 11 '12 at 08:12

score 0 · Accepted Answer · edited May 23 '17 at 12:20

Are tables that store "lookup" values faster than storing full descriptive text?

We tested that where I work. (Kind of. We didn't distinguish lookup tables from any other kind of table.) But let me point out that your question isn't about lookup tables; your question is about surrogate keys (id numbers). You can create a "lookup" table without using id numbers.

For an example with timings, see this SO question. It seems there's a tipping point. Below the tipping point, queries based on natural keys will usually run faster than queries based on id numbers. (Narrower tables and fewer joins.) But past the tipping point, joins surrogate keys run faster than natural keys.

But "faster" doesn't necessarily mean "fast". And "slower" might still be fast enough.

Thanks for the other link. That was a good read, plus gave me good grounds to Google a bit more info. — Neil Knight, Dec 14 '11 at 11:09

DB design and performance

1 Answers1