create table purchase_orderdata
(
    Id            int auto_increment
        primary key,
    PurchaseOrder varchar(255) null
);

create table purchase_boxdata
(
    Id              int auto_increment
        primary key,
    BoxNo           varchar(255) null,
    PurchaseOrderId int          null comment 'index to Purchase order',
    constraint purchase_boxdata_toOrder___fk
        foreign key (PurchaseOrderId) references purchase_orderdata (Id)
);

create index idx_boxdata_boxNo
    on purchase_boxdata (BoxNo);

create index idx_orderdata_purchaseOrder
    on purchase_orderdata (PurchaseOrder);

create table purchase_watermeterdata
(
    Id                int auto_increment
        primary key,
    SerialNo          varchar(255) null,
    WaterMeterData_id int          null,
    PurchaseBoxId     int          null,
    constraint UKgcnskfro3he4kwysdeldnvubf
        unique (WaterMeterData_id),
    constraint FKc376smwypchilry1au44u2uj6
        foreign key (WaterMeterData_id) references watermeter (Id),
    constraint purchase_watermeterdata_purchase_boxdata_Id_fk
        foreign key (PurchaseBoxId) references purchase_boxdata (Id)
);

create index idx_watermeterdata_serialNo
    on purchase_watermeterdata (SerialNo);