[PHP-DB] DB Modeling . Help!
Hi, some week's ago i was having a querying problem, it was i nightmare. Now i'm i have the same modeling problem. But his time i've stopped to think before. Right Here we go: It's 'simple'. I have a simple datagrid (5 columns x 50 rows). Each row have a label and the 5 columns values.How can i store this values in an SQL DB? I mean (the standart html output): a1| a2 | a3 | a4b| a5 Consumption Edge [3,4] [32,34] [113,40][32,4] [4,21] Consumption Out of Edge [3,4] [32,34] [113,40][32,4] [4,21] Demand Edge [3,4] [32,34] [113,40][32,4] [4,21] Demand Out of Edge[3,4] [32,34] [113,40][32,4] [4,21] Total Billed [3,4] [32,34] [113,40] [32,4][4,21] . . . . . . The actual modeling is a table with columns: *cosumption_edge_a1*,*cosumption_edge_a2 *... etc; *cosumption_out_edge_a1*,*cosumption_out_edge_a2*. etc ... But i'm thinking in a diferent model: like this: a table with the values ( 5 columns(a1,a2,a3,a4,a5) ), and another with the values. so the first table will have 5 foreign keys pointing to the values in the other talbe? What do you think? Thanks!
Re: [PHP-DB] 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 mjpelm...@gmail.com 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? CategoryRegisteredBilledTotal electricity_use 120,00 150,12102,36 electricity_demand130,00 150,12102,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: idcategory_name 1 electricity_use 2 electricity_demand ...... (where id is the primary key) bill_data table: ID_from_original_bill_tablecategory_idregistered billedtotal 1 1 120,00 150,12102,36 1 2 120,00 150,12102,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#78http://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 101102103 104 *AND* Value Table *ID | Registered | Billed | Total* 101 120,00 150,12102,36 102 130,00 150,12102,36 103 150,00 150,12102,36 104 110,00 140,12102,36 better? Thanks! 2012/9/19 Jim Giner jim.gi...@albanyhandball.com 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
[PHP-DB] Re: Many columns (as FK) vs 3x more Columns (in the same table)
Anyone? Please. 2012/9/18 Bruno Sandivilli bruno.sandivi...@gmail.com 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!
Re: [PHP-DB] Re: Many columns (as FK) vs 3x more Columns (in the same table)
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 101102103 104 *AND* Value Table *ID | Registered | Billed | Total* 101 120,00 150,12102,36 102 130,00 150,12102,36 103 150,00 150,12102,36 104 110,00 140,12102,36 better? Thanks! 2012/9/19 Jim Giner jim.gi...@albanyhandball.com 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-DB] Many columns (as FK) vs 3x more Columns (in the same table)
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!
Re: [PHP-DB] Another PDO ?
Imagine if you are building a generic database framework, so you (dont have, but) can generalize your queries functions and abstract some tables info. 2012/9/10 Graham H. mene...@gmail.com I think it's so that you could write functions as generically as possible. So you don't have to pass in the number of columns or hard code in values for number of columns, you can dynamically check the column count for each result set that gets passed in. That's my guess. On Mon, Sep 10, 2012 at 8:51 AM, Jim Giner jim.gi...@albanyhandball.com wrote: On 9/10/2012 10:49 AM, Bastien Koert wrote: On Mon, Sep 10, 2012 at 9:48 AM, Jim Giner jim.gi...@albanyhandball.com wrote: Reading up on the pdostatement class. Wondering what the intent of the columnCount function is. I mean, aren't the number of columns in a result known when you write the query? Granted, you might have some very complex query that you may not know the number, but for most queries you will know the columns you are expecting. So - what am I not seeing? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php It might be for those cases where you run a select * from ... But - again - one already knows how many fields are in that table when one writes the query... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Graham Holtslander Computer Systems Technologist www.graham.holtslander.com mene...@gmail.com
[PHP-DB] Bruno Sandivilli deseja bater papo
--- Bruno Sandivilli deseja manter contato com você de uma forma melhor usando alguns dos novos produtos mais legais do Google. Se você já tem Gmail ou o Google Talk, visite: http://mail.google.com/mail/b-bebbbe453e-455c1cf2d9-MHz-SN8BAy2MJrjWTu3LUdl5yXw Você terá de clicar neste link para poder bater papo com Bruno Sandivilli. Para obter Gmail - uma conta de e-mail gratuita do Google com mais de 2.800 megabytes de armazenamento - e bater papo com Bruno Sandivilli, visite: http://mail.google.com/mail/a-bebbbe453e-455c1cf2d9-MHz-SN8BAy2MJrjWTu3LUdl5yXw Gmail oferece: - Mensagens instantâneas incorporadas Gmail - Excelente proteção contra spam - Pesquisa incorporada para buscar mensagens e um modo útil de organizar e-mails em conversas - Nenhum anúncio em pop-ups nem banners não dirigidos - apenas anúncios de texto e informações relacionadas que são relevantes ao conteúdo das mensagens Tudo isso para você e de graça. Espere! Tem mais! Ao abrir uma conta Gmail , você também obterá acesso ao Google Talk, o serviço de mensagens instantâneas do Google: http://www.google.com/talk/intl/pt-BR/ O Google Talk oferece: - Bate-papo na web que pode ser usado em qualquer lugar, sem necessidade de download - Uma lista de contatos sincronizada com sua conta Gmail - Chamadas de voz de computador a computador gratuitas e de alta qualidade quando você fizer o download do cliente do Google Talk Estamos trabalhando com afinco para adicionar novos recursos e fazer melhorias. Por isso, também é possível que solicitemos seu feedback e sugestões periodicamente. Agradecemos sua ajuda em tornar nossos produtos ainda melhores. Obrigado, A Equipe do Google Para saber mais sobre Gmail e sobre o Google Talk, visite: http://mail.google.com/mail/help/intl/pt_BR/about.html http://www.google.com/talk/intl/pt-BR/about.html (Se você não conseguir acessar os URLs desta mensagem clicando neles, copie-os e os cole na barra de endereços do navegador). -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Bruno Sandivilli deseja bater papo
--- Bruno Sandivilli deseja manter contato com você de uma forma melhor usando alguns dos novos produtos mais legais do Google. Se você já tem Gmail ou o Google Talk, visite: http://mail.google.com/mail/b-bebbbe453e-848c4569fe-u4-qqCC7sO89q1Tgzc25hgdU7Ik Você terá de clicar neste link para poder bater papo com Bruno Sandivilli. Para obter Gmail - uma conta de e-mail gratuita do Google com mais de 2.800 megabytes de armazenamento - e bater papo com Bruno Sandivilli, visite: http://mail.google.com/mail/a-bebbbe453e-848c4569fe-u4-qqCC7sO89q1Tgzc25hgdU7Ik Gmail oferece: - Mensagens instantâneas incorporadas Gmail - Excelente proteção contra spam - Pesquisa incorporada para buscar mensagens e um modo útil de organizar e-mails em conversas - Nenhum anúncio em pop-ups nem banners não dirigidos - apenas anúncios de texto e informações relacionadas que são relevantes ao conteúdo das mensagens Tudo isso para você e de graça. Espere! Tem mais! Ao abrir uma conta Gmail , você também obterá acesso ao Google Talk, o serviço de mensagens instantâneas do Google: http://www.google.com/talk/intl/pt-BR/ O Google Talk oferece: - Bate-papo na web que pode ser usado em qualquer lugar, sem necessidade de download - Uma lista de contatos sincronizada com sua conta Gmail - Chamadas de voz de computador a computador gratuitas e de alta qualidade quando você fizer o download do cliente do Google Talk Estamos trabalhando com afinco para adicionar novos recursos e fazer melhorias. Por isso, também é possível que solicitemos seu feedback e sugestões periodicamente. Agradecemos sua ajuda em tornar nossos produtos ainda melhores. Obrigado, A Equipe do Google Para saber mais sobre Gmail e sobre o Google Talk, visite: http://mail.google.com/mail/help/intl/pt_BR/about.html http://www.google.com/talk/intl/pt-BR/about.html (Se você não conseguir acessar os URLs desta mensagem clicando neles, copie-os e os cole na barra de endereços do navegador). -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Social NetWork Db
Hi , i want to model a simple db for user with a social network. Cold anyone help me? Thanks!
[PHP-DB] Fwd: Object Class Type
-- Forwarded message -- From: Bruno Sandivilli bruno.sandivi...@gmail.com Date: 2011/3/23 Subject: Object Class Type To: php-db-sc.1300557321.bdjnjaejefnonigedkec-bruno.sandivilli=gmail.com@ lists.php.net Hi all, could someone give me a help? I have this scenario: I want to have a table with objects that are composed of any number of types(strings,ints,bool,etc) and any number of types(eg: a object Car, may have 3 types inside(string name, int doors,bool new), and in the same table could have an object of type House, tha could have 10 types inside); Thansk!
