List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 16 2012 1:10am
Subject:Re: suggestion needed for table design and relationship
View as plain text  
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.brawley@stripped>
> To: Rajeev Prasad <rp.neuli@stripped>; "mysql@stripped"
> <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.
>

Thread
suggestion needed for table design and relationshipRajeev Prasad15 Aug
  • Re: suggestion needed for table design and relationshipPeter Brawley15 Aug
    • Re: suggestion needed for table design and relationshipRajeev Prasad15 Aug
      • Re: suggestion needed for table design and relationshipPeter Brawley16 Aug