CODEWITHSUNDEEP
mysqlsqldatabaserelational-databasenormalization
Okoye Victory
Okoye Victory

Reputation: 35

Normalisation of Tables

How do I normalize these tables: Owing to the fact that I cant create a foreign key on a partial part of a Composite Key. The strong text signify the primary keys in the database.

Product(ItemCode, ItemName, SellingPrice)

Supplier(SupplierName, Phone, Address)

SupplierProducts(SupplierName, ItemCode , SupplyPrice)

Upvotes: 1

Views: 49

Answers (2)

Drew
Drew

Reputation: 24949

create table Products
(   ItemCode int auto_increment primary key,
    ItemName varchar(100) not null,
    SellingPrice decimal(10,2) not null,
    updateDt datetime not null
    -- throw in some indexes
);

create table Suppliers
(   SupplierId int auto_increment primary key,
    SupplierName varchar(100) not null -- allows you to change a supplier name
    -- throw in some indexes once you add more columns
);

create table SupplierPhone
(   id int auto_increment primary key,
    SupplierId int not null,
    PhoneNum varchar(20) not null,
    PhoneType int not null, -- have a FK somewhere
    -- FK back to Suppliers
    -- throw in some indexes
    key (SupplierId)
);

create table SupplierAddr
(   id int auto_increment primary key,
    SupplierId int not null,
    Addr1 varchar(100) not null,
    Addr2 varchar(100) not null,
    -- etc all that jazz
    AddrType int not null,  -- have a PK somewhere
    -- FK back to Suppliers
    -- throw in some indexes
    key (SupplierId)
);

create table SupplierProducts
(   id int auto_increment primary key,
    SupplierId int not null,
    ItemCode int not null,
    SupplyPrice decimal(10,2) not null,
    updateDt datetime not null,
    -- FK back to Suppliers
    -- FK back to Products
    -- throw in some indexes
    unique key (SupplierId,ItemCode) -- won't allow dupes on this combo
);

Upvotes: 1

Tudor Constantin
Tudor Constantin

Reputation: 26861

Leaving aside your choice of Supplier.SupplierName as a PK, the tables look like a perfect normal(ized) m to n relationship between the Product and the Supplier tables. Add a unique index on the pair of SupplierProducts.SupplierName and SupplierProducts.ItemCode and you're good to go

Upvotes: 0

Related Questions