One of my considerations would be whether or not the fields are 1-to-1 to the user or are non-related. In your list for example favorite politician is something non-related to the user and you might want to implement either as a search function or a pulldown list or a tabulated page. So normalizing that to it's own table and putting a foreign key in your user table makes sense and would make maintenance easier (combining duplicate names and such) and GUI entry.
Name is relatively unique so leave it alone Shoe size is just a byte so not worth normalizing and probably isn't queried much. phone is unique and also not queried much. address is mostly unique (several people at same address) so you wouldn't save much by normalizing. Why normalize: 1. Query Performance 2. Data loading performance 3. Ease of maintenance 4. When data integrity is less of a concern (such as in read-only databases) and query performance is a higher priority Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Staffan Tylen [staffan.ty...@gmail.com] Sent: Friday, November 30, 2012 9:50 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Database design preferences I'm looking for both administrative and technical advice on the pros and cons of either creating one single database table with many columns or creating multiple tables with fewer but related columns to be JOINed when needed. Assume that the data is all related 1-to-1, like name, home address, primary phone, shoe size, favourite politician (NULL accepted!), etc. At a first glance it seems logical to select a single table as it simplifies access to the data but there may be good reasons that I'm not aware of to split the data over multiple tables. I have only limited experience of SQL so any guidelines are appreciated. Thanks in advance. Staffan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
