Re: Re: Many columns (as FK) vs 3x more Columns (in the same table)

We are getting closer. But I'll give some image to better ilustrate:

http://177.71.185.219/stable/anexos/505b18fc713f8imagem.png

this is my table in Flex, the fields are in portuguese so 'ignore' it.




2012/9/19 Matt Pelmear <[email protected]>

> 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<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 <[email protected]>
>>
>>  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
>
>