OT : Advice on SQL database design

[Peter Haworth]

Hi Alex,
Your situation seems to involve a 1-1 relationship between tables which
always feels a little strange for the reasons you mentioned.

I'd probably eliminate option A for starters.

Following classic database design rules, you should probably have a
separate table for each armed forces branch containing its relevant columns
plus a foreign key linking it to the primary key of your persons table.
That's option C in your example, except that your person table would not
need a column with the name of the forces branch in it.

That structure would allow you to handle the situation where one person may
have been in 2 different branches of the forces, if that's possible in your
app.

It does make retrieval of the data related to one person more complex since
you'd need to have one JOIN statement from the person table to however many
branch tables there are.  But you could set that up as a view so you
wouldn't constantly be issuing the complex SELECT command to get all the
data.

Another possibility might be to turn things upside down.  Have a table for
each branch of the forces and have your persons table have a separate
foreign key column for each forces table that links to its primary key.
 That would enable you to, for example, quickly get a list of all the
people in the navy.

No doubt other ways to do it also.  Some of the design decisions will
depend on your application requirements.


Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>


On Mon, Jun 30, 2014 at 5:23 PM, Alex Tweedly <alex at tweedly.net> wrote:

>
> I guess there's a meta-question about "what's a good book/source on SQL
> database design ?"
>
> I am trying to design a database. There is one table which contains, let's
> say, Armed Forces Personnel
>  - each row is a different person
>  - columns include the obvious .... name, date of birth, ... that are
> common to all
>  - there is a column saying which armed force the person is in
>       - and then there are additional, DIFFERENT fields depending on which
> one it is
>
>
> Plan A.
> Just add all the additional columns.
>
> But then they're (mostly) empty! Doesn't seem right.
>
> Plan B.
> Separate tables for each set of additional data; the 'person' table has a
> column specifying which set of data exists, and then a foreign key which is
> matched to the primary key of the additional table.
>
> e.g.
> Person
> 1 : John Smith : navy : 1
> 2 : Jim Brown : army : 1
>
> Navy
> 1 : Can swim 50 yards
>
> Army
> 1 : Can walk 100 miles carrying a full pack (???)
>
> Plan C.
> Separate tables for each additional data set - but the additional tables
> share the primary key with the person table.
>
> e.g.
> Person
> 1 : John Smith : navy
> 2 : Jim Brown : army
>
> Navy
> 1 : Can swim 50 yards
>
> Army
> 2 : Can walk 100 miles carrying a full pack (???)
>
> I hope that explanation was at least half-way understandable :-)
>
> So - any comments about the choice between A, B or C ?
>    - or is there answer "D of course ...."
>
> Thanks for any help
> -- Alex.
>
>
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>