Tables that 'extend' other tables

Question

I have a table that stores information on Vendors called dbo.Vendor. Its has fields like this:

 1. VendorID
 2. VendorName
 3. VendorType
 4. AddressLine1
 5. EMail
 6. Telephone
 7. and so on....

This information is common to all vendors. But depending on the type of vendor (VendorType field) I need to collect more specific information. For example a vendor that is a charity will have a Charity Number but a vendor that are Lawyers will have some kind of legal registration number instead. If a vendor is a cinema then I may need to know seating capacity which won't apply to other vendors of course.

Do I really have to create a unique table for each of these different vendors e.g. dbo.VendorLaw, dbo.VendorCinema. Or can I create all possible fields in the main dbo.Vendor table and leave NULL values where the field does not apply to that vendor? This is breaking normalization rules of course.

Answer 1

Depending on the scope of how much additional optional info you need per vendor type, I would create another two tables: one reference table, which stores all the different types of additional info, and one table that stores all the records (and links to the main table).

CREATE TABLE schema.VendorAdditionalInfo (
   autoId serial NOT NULL,
   vendorId int,
   vendorInfoId int,
   vendorInfoText varchar
);

Then create your reference table:

CREATE TABLE schema.VendorInfo (
   vendorInfoId serial NOT NULL,
   vendorType int,
   vendorInfoName text
)

This way you can create any amount of records in VendorAdditionalInfo based on what vendor type it is.

EDIT: Example of the info you'd input:

INSERT INTO schema.VendorInfo (vendorType, vendorInfoName)
VALUES 
(1, 'Lawyer Registration Number'),
(2, 'Nurse ID Number'),
(3, 'Hot Dog Business License')

Then for your records table you'd enter your info as such:

INSERT INTO schema.VendorAdditionalInfo (vendorId, vendorInfoId, vendorInfoText)
VALUES
(10, 1, 'LAW13245'),
(11, 2, 'NURSE234234'),
(12, 1, 'LAW56156'),
(13, 3, 'HOTDOGBUSINESSLIC23')

Basically - the text field is the field that's unique for each additional info type.

Answer 2

I would create the additional tables. This allows you to enforce null/non-null (and other) constraints easily based on the vendor type - and you can even create a superkey in your existing table on (VendorID,VendorType) and a computed column in each vendor specific column to ensure that e.g. only Cinema vendors have entries in the VendorCinema table.

CREATE TABLE Vendors (
   VendorID int IDENTITY(-47,1) not null,
   VendorName varchar(19) not null,
   VendorType varchar(11) not null,
   AddressLine1 varchar(35) not null,
   EMail varchar(312) null,
   Telephone varchar(15) null,
   constraint PK_Vendors PRIMARY KEY (VendorID),
   constraint UQ_Vendor_Types UNIQUE (VendorID,VendorType),
   constraint CK_Vendor_Types CHECK (VendorType in ('Law','Cinema'))

)

and

CREATE TABLE CinemaVendors (
    VendorID int not null,
    VendorType as CONVERT(varchar(11),'Cinema') persisted,
    Seating int not null,
    BruceWillisMovies int not null,
    constraint PK_CinemaVendors PRIMARY KEY (VendorID),
    constraint FK_CinemaVendors_Vendors FOREIGN KEY
                                (VendorID,VendorType)
             references Vendors (VendorID,VendorType),
    constraint CK_BruceWillisMovies CHECK (BruceWillisMovies > 3)
)

This is far easier to do in separate tables than to have a slew of nullable columns in one single table and then trying to enforce all of the actual constraints.

This also addresses the concerns with the EAV model - where we want an int stored for cinema vendors, we're sure that an int has actually been stored.

(It's optional whether you also declare a foreign key between the two above tables based on just the VendorID column. Sometimes I do, sometimes I don't. It's the "real" foreign key, but we use the two column one above to ensure that only Cinema vendors end up in the CinemaVendors table)