[solved] Is it better to have many tables

[Joanna]

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]

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.

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]

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]

a 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][+][-]

1. SELECT m.id, a.f1,b.f2, c.f3, d.f4, e.f5, f.f6, g.f7
2.    FROM mastertable m
3.    LEFT JOIN table1 a ON m.id=a.id
4.    LEFT JOIN table2 b ON m.id=b.id
5.      ......
6.    LEFT JOIN table7 g ON m.id=g.id;
7.  

   

But in a single table, can you suggest a SQL of left joining?

[Zvoni]

Code: SQL  [Select][+][-]

1. SELECT m.id,
2. COALESCE(a.SomeValue, 0) AS f1,
3. COALESCE(b.SomeValue, 0) AS f2,
4. COALESCE(c.SomeValue, 0) AS f3,
5. COALESCE(d.SomeValue, 0) AS f4,
6. COALESCE(e.SomeValue, 0) AS f5,
7. COALESCE(f.SomeValue, 0) AS f6,
8. COALESCE(g.SomeValue, 0) AS f7
9. FROM mastertable AS m
10. LEFT JOIN childtable AS a ON m.id=a.ForeignKey_to_master AND a.WhichColumn=1
11. LEFT JOIN childtable AS b ON m.id=b.ForeignKey_to_master AND b.WhichColumn=2
12. LEFT JOIN childtable AS c ON m.id=c.ForeignKey_to_master AND c.WhichColumn=3
13. LEFT JOIN childtable AS d ON m.id=d.ForeignKey_to_master AND d.WhichColumn=4
14. LEFT JOIN childtable AS e ON m.id=e.ForeignKey_to_master AND e.WhichColumn=5
15. LEFT JOIN childtable AS f ON m.id=f.ForeignKey_to_master AND f.WhichColumn=6
16. LEFT 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][+][-]

1. SELECT m.id,
2. COALESCE(CAST(a.WhichColumn AS CHAR), 'Column not found') AS wc,
3. COALESCE(a.SomeValue, 0) AS f1
4. FROM mastertable AS m
5. LEFT JOIN childtable AS a ON m.id=a.ForeignKey_to_master AND a.WhichColumn=:pParamCol

[Joanna]

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]

Hmmm...

I hated to type more characters. Disregarding coalesce,  the SQL would be :

Code: SQL  [Select][+][-]

1. SELECT id, a.FIELD AS f1, b.FIELD AS f2, c.FIELD AS f3, ..., g.FIELD AS f7
2.   FROM mastertable m
3.   LEFT JOIN childtable a ON m.id=a.id AND a.FIELD='F1'
4.   LEFT JOIN childtable b ON m.id=b.id AND b.FIELD='F2'
5.    .......
6.   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]

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]

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 

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:

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. 

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][+][-]

1. FROM MyTable AS m
2. SELECT m.ID.....

[Joanna]

Changing the syntax could break backwards compatibility though. Probably better to just get used to the strange syntax 

[Zvoni]

Changing 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]

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.