Bruno,
This schema layout seems a little odd to me. Maybe I'm just
misunderstanding the use.
Correct me if I'm wrong here:
Each row in the "bill" table is a customer? (or some other lookup value
for which you're generating the data grid?)
I assume the 15 fields from your original message are the 20 fields you
are describing here?
The values inside the Bill table are 100% unique and point to the Value
table?
You're trying to generate output that looks like this?
Category Registered Billed Total
electricity_use 120,00 150,12 102,36
electricity_demand 130,00 150,12 102,36
...
for a particular row in the Bill table where ID=something ?
If this is true, I would consider normalizing this a bit more.
If you break the columns in your bill table out into their own table,
expand your values table a bit, you could end up with something like this:
categories table:
id category_name
1 electricity_use
2 electricity_demand
... ...
(where id is the primary key)
bill_data table:
ID_from_original_bill_table category_id registered billed total
1 1 120,00 150,12 102,36
1 2 120,00 150,12 102,36
...
(where (ID_from_original_bill_table, category_id) is the primary key)
And do queries like this:
SELECT c.category_name, bd.registered, bd.billed, bd.total
FROM categories AS c
JOIN bill_data AS bd ON c.id=bd.category_id
WHERE bill_data.ID_from_original_bill_table="whatever";
Or am I still not understanding correctly what you're trying to do?
If you actually need to do a pivot you might want to look here:
http://www.artfulsoftware.com/infotree/queries.php#78
-Matt
On 09/19/2012 01:33 PM, Bruno Sandivilli wrote:
Sorry for the lack of explanation.
Again:
I have to model a bill table.
The bill have 20 fields.
But for each field we have: *registered* value, *billed *value and* total
value.*
So i've created an *bill* table with *20 *fields (representing the bill
fields, like: consumption, demand, etc ), and a *bill_values* table (with
*id*,*registered*,*billed *and *total*).
I'll try to ilustrate:
Bill Table
*ID | eletricity_use | eletricity_demand_ | eletricity_traffic_use
| eletricity_penalties *| etc ........
1 101 102 103
104
*AND*
Value Table
*ID | Registered | Billed | Total*
101 120,00 150,12 102,36
102 130,00 150,12 102,36
103 150,00 150,12 102,36
104 110,00 140,12 102,36
better? Thanks!
2012/9/19 Jim Giner <jim.giner@xxxxxxxxxxxxxxxxxx>
On 9/19/2012 3:12 PM, Jim Giner wrote:
On 9/18/2012 8:52 AM, Bruno Sandivilli wrote:
Hi, i strugling to decide what is the best choice:
I have a 15 row x 3 columns Flash DataGrid, it means, for each row i
have 3
values.
To represent this in my Database, I could:
1. Create 2 Tables : A Values table - with 3 columns ; and a Bill
table
(with 15 foreign keys, each one pointing to one row in the Values
table).
2. Create one Table with 45 columns (15 fields * 3 values for each
field).
I want to know, wich is the best choice?
To manage my query, now i have a SELECT with a thousand of leftJoins.
This is the best choice?
How could I run a query wich will give all results linked, like:
( column_1_val_1, column_1_val_2, column_1_val_2,
column_2_val_1, column_1_val_2, column_1_val_3,
etc...) ?
Thanks!
I don't get it either.
You have 15 rows with 3 cols each. So? Display them. Query them. Big
deal.
What is the real difficulty here? I'm not seeing it.
(I don't read it as 15x3x3 - at least that's not what he said since he
said 'for each row he has 3 values')
Continuing on - why is your visual structure at this point NOT the same as
your physical structure? I don't think you're telling us what your real
problem is here. We need more information. You have '15 row with 3 cols
each', ie, 'each row has 3 values'. So - your table has 15 records in it,
each with 3 columns. Add a key field to give each row an identity and
that's it. No?
Sorry - but again - this post is not showing me a clear problem.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
