Database Design Advice

[Smylers]

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 stripey.com> 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
trigger.

> 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
that.

Smylers
-- 
The UK gov's proposed gagging law will suppress protest and campaigning by
charities and political groups. Read more: http://civilsocietycommission.info/
Please sign this petition: http://38d.gs/1cJ9ViA
Oxfam • BHA • Quakers * Countryside Alliance • League Against Cruel Sports • CND