[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
Re: [sqlite] Database design preferences
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
Re: [sqlite] Database design preferences
On 30 Nov 2012, at 3:50pm, Staffan Tylen staffan.ty...@gmail.com wrote: 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. You should be able to keep the schema of a table in your head at one time. So no tables with 30 or more columns. Finding a table with numbered columns, especially, is usually a sign of poorly designed schema. There are speed and handling problems with tables with more than one BLOB column. It's feasible, under some circumstances, that you might want to spin multiple-BLOBs-per-record into a second table, or keep only one BLOB in the first table. Apart from that, SQLite is pretty efficient at 'wide' tables as long as you keep the column count below 30 or so. There's really not that much to worry about and no need to do JOIN in cases where you have a 1-to-1 relationship. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database design preferences
On 11/30/12 8:34 AM, Simon Slavin slav...@bigfraud.org wrote: On 30 Nov 2012, at 3:50pm, Staffan Tylen staffan.ty...@gmail.com wrote: 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. snip Apart from that, SQLite is pretty efficient at 'wide' tables as long as you keep the column count below 30 or so. There's really not that much to worry about and no need to do JOIN in cases where you have a 1-to-1 relationship. I use SQLite as a data store for a simulation model. I have a number of different kinds of simulation entity, and then many results that are computed during simulation. These results are often 1-to-1 with a given kind of entity, but in practice I create one table for the entity definitions, and separate tables for each kind of result. This allows a nice separation of concerns: I never need to puzzle over where a particular column's value comes from. It's either a scenario input, or from the particular model with which the table is associated. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
