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