From: Peter Brawley Date: August 16 2012 1:10am Subject: Re: suggestion needed for table design and relationship List-Archive: http://lists.mysql.com/mysql/228003 Message-Id: <502C488D.9060409@earthlink.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 2012-08-15 5:27 PM, Rajeev Prasad wrote: > hello Peter, > > data_specific_to_device = contains diff values, and is different for different devices > associated_service = will also be a list of values which will be different for different devices > > and same for associated_device column. > > this data rarely(almost never) changes and add happens only say twice a month. So can I not have one big table like below? Size is not the issue. A basic table design rule is atomicity--one value per cell. Violating that rule screws up queries. > what disadvantage does it has? sorry I am not into RDBMS, Well you are now :-). Rilly you have three choices--read about normalisation enough to do it right, hire someone to do it right, or botch the system. > so i want to know prob specific to my data and proposed table layout. I am also not clear about relating more than one tables, if i break this up in more than one table.... create table parent( deviceID int unsigned primary key auto_increment, devx, ... ) engine=innodb; create table child child( childID int unsigned primary key auto_increment, deviceID int unsigned, foreign key(deviceID) references parent(deviceID) on update cascade on delete cascade, ... ) engine=innodb; PB ---------------- > > > thx in advance. > Rajeev > > > ________________________________ > From: Peter Brawley > To: Rajeev Prasad ; "mysql@stripped" > Sent: Wednesday, August 15, 2012 4:01 PM > Subject: Re: suggestion needed for table design and relationship > > On 2012-08-15 1:54 PM, Rajeev Prasad wrote: >> I have to keep this data in MySql, and i am not sure (as SQL/databse is not my field) how to organise this into one or many tables? right now I would represent my info as follows: >> >> device_name|HW_version|SW_version|IP_addr_pvt|IP_addr_pub|data_specific_to_device|associated_service |associated_device >> dev_x | 1234 |1234 |1.2.3.4 |9.8.7.6 |data_specific_to_x |SVC_A,SVC_B,SVC_C... |dev_y,dev_z,dev_n,dev_m... >> dev_y | 2348 |7734 |10.2.3.4 |99.8.7.6 |data_specific_to_y.....|SVC_B,SVC_X... |dev_x,dev_m... >> dev_z | 3934 |5634 |11.2.3.4 |79.8.7.6 |data_specific_to_z.....|SVC_M |dev_n,dev_m... >> ... >> >> >> pl advice. what would be the best design? data_specific_to_device could be more than one column, as i get to explore the data a bit more. >> >> and do i really need a device_id field? whi. ch any SQL table normally has. > Without a primary key, a table isn't really a table. A surrogate > (auto_increment) PK might be simplest. > > If the associated_service column is a list of values, it needs to be > projected to a child table (parentkey, data item ...). > > From the info posted, I can't tell much about the other fields > > PB > > ----- > >> ty. >