mysql - 此架构和DBIC代码是否有效？

我试图确定我是否以正确的方式处理SQL和DBIx：：Class的以下问题，因为我对这两个问题都很陌生。
我有一个包含3个表、设备、对象和网络的模式
一个设备可以有0..1到多个对象
一个对象可以有1个网络，但一个网络可以属于0…许多设备。
我试图在图中所示的模式中捕捉这种关系。
这是相关的模式。
设备

__PACKAGE__->table("Device");

__PACKAGE__->add_columns(
  "devicename",
  { data_type => "varchar", is_nullable => 0, size => 50 },
  "devicetype",
  { data_type => "varchar", is_nullable => 0, size => 20 },
  "deviceid",
  {  data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
);

__PACKAGE__->set_primary_key("deviceid");
__PACKAGE__->has_many(
  "objects",
  "TestApp::Schema::Result::Object",
  { "foreign.device_deviceid" => "self.deviceid" },
  { cascade_copy => 0, cascade_delete => 0 },

对象

__PACKAGE__->table("Object");

__PACKAGE__->add_columns(
  "objectid",
  { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
  "objectname",
  { data_type => "varchar", is_nullable => 0, size => 100 },
  "objecttype",
  { data_type => "varchar", is_nullable => 0, size => 20 },
  "device_deviceid",
  {  data_type => "integer", is_foreign_key => 1, is_nullable => 0 },
  "network_networkid",
  {    data_type => "integer", is_foreign_key => 1, is_nullable => 1 },
);

__PACKAGE__->set_primary_key("objectid");

__PACKAGE__->belongs_to(
  "device_deviceid",
  "TestApp::Schema::Result::Device",
  { deviceid => "device_deviceid" },
  { is_deferrable => 1, on_delete => "NO ACTION", on_update => "NO ACTION" },
);

 __PACKAGE__->belongs_to(
  "network_networkid",
  "TestApp::Schema::Result::Network",
  { networkid => "network_networkid" },
  {
    is_deferrable => 1,
    join_type     => "LEFT",
    on_delete     => "NO ACTION",
    on_update     => "NO ACTION",
  },
);

网络

__PACKAGE__->table("network");

__PACKAGE__->add_columns(
   "networkid",
 { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
  "network",
  { data_type => "varchar", is_nullable => 1, size => 15 },
  "netmask",
  { data_type => "varchar", is_nullable => 1, size => 15 },
  "cidr",
   { data_type => "integer", is_nullable => 1 },
 );

__PACKAGE__->set_primary_key("networkid");

__PACKAGE__->has_many(
 "objects",
  "TestApp::Schema::Result::Object",
  { "foreign.network_networkid" => "self.networkid" },
  { cascade_copy => 0, cascade_delete => 0 },
);

对于我的测试数据，我正在插入如下所示的数据。有没有更有效的方法？

#get result sets
my $network_rs = $schema->resultset('Network');
my $device_rs = $schema->resultset('Device');

#create a new device
my $new_device = $device_rs->create( { devicename => 'test_device', devicetype => 'test_dt'});

#create a new network if it doesn't exist.
my $new_network = $network_rs->find_or_create({ network => '1.1.1.1', netmask => '255.255.255.0', cidr => '24' });

#Add two objects and set the foreign key for the network table for the newly created network above.
$new_device->objects->create( { objectname => 'networkobj1',network_networkid => $new_network->networkid });
$new_device->objects->create( { objectname => 'networkobj2',network_networkid => $new_network->networkid });

要查看分配给每个网络的网络，我只需执行以下操作；

my @deviceObjects = $new_device->objects();

foreach my $object (@deviceObjects)
{
print $object->objectname . " contains the following network:\n";
print $object->network_networkid->network . "\n";
print $object->network_networkid->netmask . "\n";
print $object->network_networkid->cidr . "\n";
}

最佳答案

基本上你做的每件事都是正确的。
对象表通常称为桥或多对多辅助表。
要直接从设备到其网络，可以添加many-To-many helper方法，请参阅DBIx：：Class：：Relationship文档。
有一些模块可以用（测试）数据填充数据库，例如DBIx：：Class：：Fixtures。
推荐的关系命名（以及DBIx：：：：模式：：从现有数据库生成的加载器）对于返回一行（HasyMulk）的关系返回单个行（RealsSyto，HasgOne和MyLyWAES）和复数是单数的。
使用device->objects->network读起来更自然，包括存储外键的列的名称没有多大意义。
除了这些小事，还有一个好的开始！
加入irc.perl.org中的dbix类以获取实时帮助。