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 <peter.braw...@earthlink.net>
To: Rajeev Prasad <rp.ne...@yahoo.com>; "mysql@lists.mysql.com"
<mysql@lists.mysql.com>
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
