I''m pretty new to developing web apps and need help with my database
table design. I''m working on an inventory application to manage WLAN
hardware. I''ve created a table to hold information on each device with
the following (model refers to the manufacturer''s model number):
create table devices (
id int not null auto_increment,
hostname varchar(64) null,
mfg_model_id int not null,
serial_number varchar(20) null,
location varchar(20) not null,
shipped_on date null,
purchase_price decimal(10,2) not null,
purchased_on date null,
quote_name varchar(20) null,
purchase_order_name varchar(20) null,
ip_address char(64) null,
deployed_on date null,
constraint fk_devices_mfg_model foreign key (mfg_model_id)
references mfg_models(id)
,
primary key (id)
);
create table models (
id int not null unique,
description char(20) not null,
category char(20) not null,
primary key (id)
);
The models table will be statically populated like:
insert into mfg_model (id,description,category) values
(''10'',''AIR-WLC4402'',''Switch'');
insert into mfg_model (id,description,category) values
(''11'',''AIR-WLC4404'',''Switch'');
insert into mfg_model (id,description,category) values
(''30'',''AP1020'',''AP'');
insert into mfg_model (id,description,category) values
(''31'',''AP1030'',''AP'');
This way, when I generate the view to create a new device, I can have a
drop-down menu pre-populated with all the device mfg_model.
What I don''t understand is how the relationship between the two tables
should be set up. Semantically, I''d think that each device has_one
mfg_model. This is a one-way relationship, where the value of
device.mfg_model_id simply refers to a list of static attributes and
saves space in my device table.
Is this right? I''ve let myself get confused because every code example
I''ve found shows a corresponding "belongs_to_many"
relationship pointing
in the other direction. Do I need one in this case?
Thanks!
-Mason
--
Posted via http://www.ruby-forum.com/.
I realized a couple of typos in the example above related to the name
and pluralization of the "mfg_models" table.... Corrected version
here:
create table devices (
id int not null auto_increment,
hostname varchar(64) null,
mfg_model_id int not null,
serial_number varchar(20) null,
location varchar(20) not null,
shipped_on date null,
purchase_price decimal(10,2) not null,
purchased_on date null,
quote_name varchar(20) null,
purchase_order_name varchar(20) null,
ip_address char(64) null,
deployed_on date null,
constraint fk_devices_mfg_model foreign key (mfg_model_id)
references mfg_models(id),
primary key (id)
);
create table mfg_models (
id int not null unique,
description char(20) not null,
category char(20) not null,
primary key (id)
);
The mfg_models table will be statically populated like:
insert into mfg_models (id,description,category) values
(''10'',''AIR-WLC4402'',''Switch'');
insert into mfg_models (id,description,category) values
(''11'',''AIR-WLC4404'',''Switch'');
insert into mfg_models (id,description,category) values
(''30'',''AP1020'',''AP'');
insert into mfg_models (id,description,category) values
(''31'',''AP1030'',''AP'');
--
Posted via http://www.ruby-forum.com/.
Hi, I''m not sure but acts_as_dropdown may help you. http://jroller.com/page/obie?entry=acts_as_dropdown Daisuke On Sun, 18 Jun 2006 22:15:18 +0200 Mason Brown <mason@nanite.net> wrote:> I''m pretty new to developing web apps and need help with my database > table design. I''m working on an inventory application to manage WLAN > hardware. I''ve created a table to hold information on each device with > the following (model refers to the manufacturer''s model number): > > create table devices ( > id int not null auto_increment, > hostname varchar(64) null, > mfg_model_id int not null, > serial_number varchar(20) null, > location varchar(20) not null, > shipped_on date null, > purchase_price decimal(10,2) not null, > purchased_on date null, > quote_name varchar(20) null, > purchase_order_name varchar(20) null, > ip_address char(64) null, > deployed_on date null, > constraint fk_devices_mfg_model foreign key (mfg_model_id) > references mfg_models(id) > , > primary key (id) > ); > > create table models ( > id int not null unique, > description char(20) not null, > category char(20) not null, > primary key (id) > ); > > The models table will be statically populated like: > > insert into mfg_model (id,description,category) values > (''10'',''AIR-WLC4402'',''Switch''); > insert into mfg_model (id,description,category) values > (''11'',''AIR-WLC4404'',''Switch''); > insert into mfg_model (id,description,category) values > (''30'',''AP1020'',''AP''); > insert into mfg_model (id,description,category) values > (''31'',''AP1030'',''AP''); > > This way, when I generate the view to create a new device, I can have a > drop-down menu pre-populated with all the device mfg_model. > > What I don''t understand is how the relationship between the two tables > should be set up. Semantically, I''d think that each device has_one > mfg_model. This is a one-way relationship, where the value of > device.mfg_model_id simply refers to a list of static attributes and > saves space in my device table. > > Is this right? I''ve let myself get confused because every code example > I''ve found shows a corresponding "belongs_to_many" relationship pointing > in the other direction. Do I need one in this case? > > Thanks! > > -Mason > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails-- Daisuke Yamazaki <yamajaki@gmail.com>