[solved] Is it better to have many tables lazarus / 16-Databases ---------------------------------------------------------------------- Joanna · 2024-09-30 00:51 Hi everyone, I have a question sort of related to my previous thread here. I have a very large table that contains seven non null integer columns. Most of the values in these columns is zero and the zero values are not important to me and could just as well be null. Would it be better to transform the 7 columns  into 7 extra tables corresponding to each column containing the unique key for rows and non zero values for each column and then somehow join them together on the key at runtime using a view?  I’m not sure how this would work for the blank values. ---------------------------------------------------------------------- egsuh · 2024-09-30 01:45 QuoteWould it be better to transform the 7 columns  into 7 extra tables corresponding to each column containing the unique key for rows and non zero values for each column and then somehow join them together on the key at runtime using a view?  I’m not sure how this would work for the blank values. I think so. Blank values are simply missing --- they do not exist. There are some guidelines on designing relational database structure, and "remove null cells" is one of the principles, which is not easy to follow always (at my level ). If necessary, you may define a view, which contains all the 7 columns, and operations on separate tables would be much  easier, I guess. ---------------------------------------------------------------------- Zvoni · 2024-09-30 06:35 An Alternative might be a single Child-Table, but with an additional Field indicating the "Column". Whichever "column" is missing get's translated to a zero. Note: This would involve 7 LEFT JOINs Easy to setup, easy to create a VIEW Always remember: There is a difference how soemthing is stored, and how something is displayed ---------------------------------------------------------------------- egsuh · 2024-09-30 07:19 Quotea single Child-Table, but with an additional Field indicating the "Column". At first, I thought seven separate tables would be easier to apply left joins Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT m.id, a.f1,b.f2, c.f3, d.f4, e.f5, f.f6, g.f7    FROM mastertable m    LEFT JOIN table1 a ON m.id=a.id   LEFT JOIN table2 b ON m.id=b.id     ......   LEFT JOIN table7 g ON m.id=g.id;      But in a single table, can you suggest a SQL of left joining? ---------------------------------------------------------------------- Zvoni · 2024-09-30 07:32 Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT m.id, COALESCE(a.SomeValue, 0) AS f1,COALESCE(b.SomeValue, 0) AS f2, COALESCE(c.SomeValue, 0) AS f3, COALESCE(d.SomeValue, 0) AS f4, COALESCE(e.SomeValue, 0) AS f5, COALESCE(f.SomeValue, 0) AS f6, COALESCE(g.SomeValue, 0) AS f7FROM mastertable AS m LEFT JOIN childtable AS a ON m.id=a.ForeignKey_to_master AND a.WhichColumn=1LEFT JOIN childtable AS b ON m.id=b.ForeignKey_to_master AND b.WhichColumn=2LEFT JOIN childtable AS c ON m.id=c.ForeignKey_to_master AND c.WhichColumn=3LEFT JOIN childtable AS d ON m.id=d.ForeignKey_to_master AND d.WhichColumn=4LEFT JOIN childtable AS e ON m.id=e.ForeignKey_to_master AND e.WhichColumn=5LEFT JOIN childtable AS f ON m.id=f.ForeignKey_to_master AND f.WhichColumn=6LEFT JOIN childtable AS g ON m.id=g.ForeignKey_to_master AND g.WhichColumn=7 If the predominant "Value" is really a "0", and they are completely unimportant to be stored, it would also reduce the Data-Volume stored by a significant factor. The main difference between the "multi-table" and "single-table" approach is 1) adding/reducing the number of "Columns" --> if you want to display 8 Columns, you would have to create a table for the 8th column in multi-table. In the single-table approach you don't. You just change the Query --> NotaBene: In both approaches you have to change your Query, adding the 8th Column in the Select as well as adding the 8th LEFT JOIN 2) Use of Parameters! the single-table-approach allows full use of parameters, in case you want to display only a specific column Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT m.id, COALESCE(CAST(a.WhichColumn AS CHAR), 'Column not found') AS wc,COALESCE(a.SomeValue, 0) AS f1 FROM mastertable AS m LEFT JOIN childtable AS a ON m.id=a.ForeignKey_to_master AND a.WhichColumn=:pParamCol ---------------------------------------------------------------------- Joanna · 2024-09-30 07:46 Thanks for the answers.  Ah yes I hadn’t even thought of that, I was thinking about how redundant it would be to have 7 tables with a unique identifier column in each one.. I think the child table is definitely a better idea. The coalesce transforms null values into zeros I presume. Without the coalesce they would remain null? That’s a bit hard to get used to the way that sql lets you start using m before you have declared what it is. ---------------------------------------------------------------------- egsuh · 2024-09-30 08:13 Hmmm... I hated to type more characters. Disregarding coalesce,  the SQL would be : Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT id, a.FIELD AS f1, b.FIELD AS f2, c.FIELD AS f3, ..., g.FIELD AS f7  FROM mastertable m  LEFT JOIN childtable a ON m.id=a.id AND a.FIELD='F1'  LEFT JOIN childtable b ON m.id=b.id AND b.FIELD='F2'   .......  LEFT JOIN childtable g ON m.id=b.id AND b.FIELD='F7'; which is much more complex than my previous example  But if it could be saved as a view or stored procedure, or created on the fly, typing is not an issue. Aren't there any differences in performance-related issues? ---------------------------------------------------------------------- Joanna · 2024-09-30 08:36 I have no idea how much slower joining is than having everything in one table ... or hopefully it would be faster because without all the zero values there should be much less to join... I have no idea ---------------------------------------------------------------------- Zvoni · 2024-09-30 09:19 Quote from: Joanna on September 30, 2024, 10:36:47 amI have no idea how much slower joining is than having everything in one table ... or hopefully it would be faster because without all the zero values there should be much less to join... I have no idea  It can be really fast, if you provide a combined index, consisting of the Foreign Key and the Column-Identifier (which together should have Attribute UNIQUE!) EDIT: Quote from: Joanna on September 30, 2024, 09:46:01 amThat’s a bit hard to get used to the way that sql lets you start using m before you have declared what it is.  That's an age-old complaint about SQL. IIRC, there are even some discussions to heave SQL up to the "next" Level, bringing it more in line of what you are talking about. kinda like Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i FROM MyTable AS mSELECT m.ID..... ---------------------------------------------------------------------- Joanna · 2024-09-30 22:49 Changing the syntax could break backwards compatibility though. Probably better to just get used to the strange syntax ---------------------------------------------------------------------- Zvoni · 2024-10-01 06:17 Quote from: Joanna on October 01, 2024, 12:49:58 amChanging the syntax could break backwards compatibility though. Probably better to just get used to the strange syntax  I don't think it was changing the Syntax. More like "extending" so that "legacy"-code would still work ---------------------------------------------------------------------- Joanna · 2024-10-01 09:03 That’s one of the good things about sql it’s backwards compatibility but of course more ways to do things will add to complexity.