Practical - Design - With Dirty Hands

[flebber]

Hi

Just trying to find some good information on database design. I have
got a decent grasp on table layout design and normalization. Well as
much as a newbie can, my current project I am working on though I
notice I need a better understanding of 'bringing it together' that is
creating good multi field relationships and creating an effective
model.

Are there any good resources web/books/video which demonstrate in
practical terms how to do this?

Need to get my hands dirty to ensure my understanding and I would
prefer some more complex examples that move beyond the simple
customers/orders/inventory basic examples that are often prevalent and
unrealistically simple. This is my first project for my college
diploma and even at this semi basic level those types of examples
aren't helpful.

they say we learn best by copying and adapting off examples and others
more experienced, but I can't find the resources that do this well.

Sayth

[Fred.]

You need to recognize that your data model is a model of some area
of your business. That means that unless you are a one-person business that
you must recobnize that others, not you, may be the experts on the business
components: enttities, relationships, processes, and business rules that apply
in this area.

Conversation with these experts, even in areas you think you understand well,
are important because what appear to be solid may be transitory. For instance,
the meaning of an attribution which you think you understand may vary with
context. Looking at the design for another business probably won't help you
nearly as much as listening to these people. You need to be tactful. You
may be asking some very intimate questions from their point of view. So, you
need to make it clear you are asking about their processes to facilitate them
rather than to critique them.

Every database standard I have ever worked with has required a designed
identity for a primary key and used this for the basis of implementing
relationships. Natural-seeming identifiers have a tendency to change with
the business. For instance, a business may suddenly have an overlapping
old and a new series of invoice numbers, making the invoice number useless
for a primary key in your database.

A mulit-field identifier is subject to the same risks as other "natural"
identifiers but increased by the multiple fields. So, multi-field
realitonships should be reserved for emergencies when you must denormalize
and, so, need to enforce consistency beween denormalied entities.

Fred.

[Roy Hann]

Fred. wrote:

> On Wednesday, April 25, 2012 11:47:51 PM UTC-4, flebber wrote:
>> Hi
>>
>> Just trying to find some good information on database design. I have
>> got a decent grasp on table layout design and normalization. Well as
>> much as a newbie can, my current project I am working on though I
>> notice I need a better understanding of 'bringing it together' that is
>> creating good multi field relationships and creating an effective
>> model.
>>
>> Are there any good resources web/books/video which demonstrate in
>> practical terms how to do this?
>>
>> Need to get my hands dirty to ensure my understanding and I would
>> prefer some more complex examples that move beyond the simple
>> customers/orders/inventory basic examples that are often prevalent and
>> unrealistically simple. This is my first project for my college
>> diploma and even at this semi basic level those types of examples
>> aren't helpful.
>>
>> they say we learn best by copying and adapting off examples and others
>> more experienced, but I can't find the resources that do this well.
>>
>> Sayth
>
> You need to recognize that your data model is a model of some area
> of your business.

(And we're off! :-)

That is two incorrect and misleading statements right off the bat.

First of all the "data model" is pretty clearly the relational model.
You are referring to the conceptual model or something else, but not
the data model.

Secondly, if he is using the relational model then he does NOT want to
model some area of the business, he wants to model the form of the
assertions he needs to be able to make *about* the part of the business
that is of interest.

> That means that unless you are a one-person business that
> you must recobnize that others, not you, may be the experts on the business
> components: enttities, relationships, processes, and business rules that apply
> in this area.

Wrong too, though your next paragraph does get nearer the truth.
In general NO ONE understands a business. Each person involved will in
general have a different point of view of everything. The picture of
the business that eventually emerges is unlikely to resemble anything
any one person said about it. However it will accommodate everything
anyone wants to say about it.

This is important to point out to a newbie because they will otherwise
be distressed that their model doesn't look "right".

> Conversation with these experts, even in areas you think you understand well,
> are important because what appear to be solid may be transitory. For instance,
> the meaning of an attribution which you think you understand may vary with
> context. Looking at the design for another business probably won't help you
> nearly as much as listening to these people. You need to be tactful. You
> may be asking some very intimate questions from their point of view. So, you
> need to make it clear you are asking about their processes to facilitate them
> rather than to critique them.
>
> Every database standard I have ever worked with has required a designed
> identity for a primary key and used this for the basis of implementing
> relationships.

Please name these standards so that we can properly criticise them for
introducing a means of making data corruption and logical modelling
errors undetectable.

> Natural-seeming identifiers have a tendency to change with
> the business.

If the business changes the conceptual model has to change. Hiding the
changes in the application code and coopering up the database design
to get away with it is a professional misconduct and a sure source of
peformance and logical errors. It is possibly also a violation of
federal law in the US (e.g. Sarbanes-Oxley).

> For instance, a business may suddenly have an overlapping
> old and a new series of invoice numbers, making the invoice number useless
> for a primary key in your database.

No, it invalidates the conceptual model and requires it to be fixed
properly.

> A mulit-field identifier is subject to the same risks as other "natural"
> identifiers but increased by the multiple fields.

Nonsense. You are sweeping the data quality issue under the carpet
instead of addressing it. Natural identifiers (composite or
otherwise) are superior precisely BECAUSE they can be known to be wrong.
Synthetic "identifiers" conceal the error forever. You are adocating
using precautionary anaesthetic instead of taking careful aim when using
a hammer.

> So, multi-field
> realitonships should be reserved for emergencies when you must denormalize
> and, so, need to enforce consistency beween denormalied entities.

Utter, utter twaddle.

OP: use natural keys where possible, and if it's an SQL database make
sure the foreign keys are declared ON UPDATE CASCADE. The guidelines
for choosing keys are: uniqueness, stability, familiarity and
simplicity. (And stability does not mean immutability.)



--
Roy

[flebber]

> OP: use natural keys where possible, and if it's an SQL database make
> sure the foreign keys are declared ON UPDATE CASCADE.  The guidelines
> for choosing keys are: uniqueness, stability, familiarity and
> simplicity.  (And stability does not mean immutability.)
>
> --
> Roy

So thats sort of interesting regarding stability. Most of my model is
fairly sound but it's in managing players teams, coaches and games in
my model that has caused me the most concern in understanding and
application.

Essentially while the person(player) may be stable his role position
and team are not. So a player should be signed to a team for a set
period of time(season) however this is not guaranteed(may transfer),
the players position might change or might not play at all. Because of
this I created the players table listing only characteristics of the
person(first name, last name, age, weight, height), I then created a
team table. To manage the players to team I created a junction table
of players and teams which included a start and end date so a player
was only bound to a club until I supply an end date.

However because of this the PlayersTeams junction tables foreign key
was no longer specific to just the player but also the team, Therefore
when using the ID field from PlayersTeams in games to access in
GamesTable it needs to access the team name(for homeTeam & awayTeam,
and the teamCpatain home & away. In the PlayersTeams table the team
and players fields are only INT fields that reference the ID's from
their respective team and players tables.

Being new this is where I started to have trouble and definitely
looking for more info about design.

Sayth

[Roy Hann]

flebber wrote:

>> OP: use natural keys where possible, and if it's an SQL database make
>> sure the foreign keys are declared ON UPDATE CASCADE.  The guidelines
>> for choosing keys are: uniqueness, stability, familiarity and
>> simplicity.  (And stability does not mean immutability.)

> So thats sort of interesting regarding stability. Most of my model is
> fairly sound but it's in managing players teams, coaches and games in
> my model that has caused me the most concern in understanding and
> application.
>
> Essentially while the person(player) may be stable his role position
> and team are not. So a player should be signed to a team for a set
> period of time(season) however this is not guaranteed(may transfer),
> the players position might change or might not play at all. Because of
> this I created the players table listing only characteristics of the
> person(first name, last name, age, weight, height), I then created a
> team table. To manage the players to team I created a junction table
> of players and teams which included a start and end date so a player
> was only bound to a club until I supply an end date.
>
> However because of this the PlayersTeams junction tables foreign key
> was no longer specific to just the player but also the team, Therefore
> when using the ID field from PlayersTeams in games to access in
> GamesTable it needs to access the team name(for homeTeam & awayTeam,
> and the teamCpatain home & away. In the PlayersTeams table the team
> and players fields are only INT fields that reference the ID's from
> their respective team and players tables.

Health warning: this is Usenet. It is peopled with a diverse collection
of real experts, charlatans, self-proclaimed experts, experienced
professionals, tireless oafs, smart-alecs, ivory tower academics, zanies
and trolls.

You must make you own decision about what I am.

Ordinarily I wouldn't try to help anyone with a real design problem but
as this is plainly an exercise I'll give my more (or less) valuable
opinion for free.

Exercises are sometimes harder than real world projects because
they are chosen especially to focus on intrinsically difficult problems
that are a small part of database design in the real world. Also you
know this problem is artificial so you will be constantly
second-guessing your solution.

Having said that, here are some comments:

Focus on the stated problem (the nearest you are going to get to the
part of the business that is of interest). Ask yourself what
statements you can make about each part, and what questions you need to
answer.

Do NOT engage in conjectures about unstated problems and complications.
That is a very important discipline and if you can master yourself to
stick to only what you were told to focus on, it will make your job
easier. It is very tempting to bring your own knowledge to bear, and to
speculate about complications, but resist it vigorously. (In the real
world you would of course check with the customer to make sure they've
not overlooked something.)

So, knowing only what you've said about the problem, I suggest that the
team table is easy. It's identity is unambiguous, so you can use
natural keys (such as its name--although in another frame of reference
its name is actually a synthetic key!).

You can use its name as a foreign key in your other tables too (but
remember ON UPDATE CASCADE). It *could* change, but not ofen, so it's
stable. It's certainly familiar. It is required to be unique. So it's
a good key.

Players are way more tricky. People always are. Depending on the
business process you may be justified introducing a synthetic key for
the players. You would of course have to have some business process to
ensure that you don't accidentally create a row for the same player
twice, with a different synthetic key each time, but that's not a
database design problem.

But as this is a database that is intrinsically dealing with small
numbers of people, you *might* be perfectly justified to argue that in
THIS enterprise of interest no two players will have the same name AND
the same home phone number. Yes--you can propose all kinds of more or
less far-fetched ways that assumption is not guaranteed to he true for
all time time in the universe, but within your narrow focus it might be
acceptable.

If it is not--for reasons that *actually* obtain (as opposed to
hypothetical reasons that are not known to actually obtain but are
merely conceivable) then you would be justified in introducing a
synthetic key, subject to the business process I mentioned above.

HTH

--
Roy

[paul c]

On 27/04/2012 6:00 AM, Roy Hann wrote:
> Do NOT engage in conjectures about unstated problems and complications.

Yes, it would be better if the OP posted the original assignment
question, less conjecture that way. And the instructor might appreciate
not having to plough through solutions to problems that weren't posed.

(Though I suppose announcing disjointed requirements in dribs and drabs
does mimic the undisciplined practices of many commercial design efforts.)