Database Design Advice

Smylers Smylers at
Fri Nov 8 14:04:58 GMT 2013

Dirk Koopman writes:

> It all depends on the application and your view as to where the
> business might go in the future.

Database design should only be performed by a qualified soothsayer?

James Laver writes:

> On Fri, Nov 8, 2013 at 11:43 AM, Smylers <Smylers at> wrote:
> > • Separate fields for discount_percentage and discount_amount. This
> >   has the disadvantage of needing to ensure that a record doesn't
> >   have both fields set.
> >
> >   If the business concocts another discount type in future, this
> >   will require a field for each discount type.
> I'd probably actually go for this and a trigger.

Unless somebody else points out a compelling reason to pick a different
option, I'll do this one then.

Though a CHECK constraint should suffice for for enforcing that at least
one of the fields be NULL, so I'll probably go with that rather than a

> The particular case of discounts means it's unlikely they'll add
> another,

You sound more confident in your soothsaying skills than I am in mine!
In the field of business this is for, I could just about see “days” not
being a completely insane way of describing a discount, but whether
that's likely to happen is a different matter. (I'm certainly not about
to suggest it.)

Dirk Koopman writes:

> What I am trying to say is: do something that doesn't back you into a
> corner that you hadn't anticipated.

Indeed — that's basically my reason for asking these questions. Having
worked on so many systems where I've been backed into a corner by
database design that predates my involvement, I'm trying to avoid doing
that on this project where I'm in the rare position of creating the
initial database from scratch.

> Oh and normalising tables an all in databases is all very fine and
> gives one interlecktuwal warm feelings, but it doesn't necessarily
> give the *business* an optimal result.

James Laver writes:

> Relational databases aren't always great for modelling things, so when
> it's unclear what's the best solution, it's usually best to go for
> simple.

I think most of the times I've been scuppered by an irritating database
design, it's been through too little normalization rather than too much.
Hence my concern with these questions that I was going to do something
non-normal and regret it later. So I'm pleased with the reassurance that
doing this is reasonable.

Thanks, both. Hope you don't mind my intermingling your messages like

The UK gov's proposed gagging law will suppress protest and campaigning by
charities and political groups. Read more:
Please sign this petition:
Oxfam • BHA • Quakers * Countryside Alliance • League Against Cruel Sports • CND

More information about the mailing list