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 <a...@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@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your > subscription preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode > _______________________________________________ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
