Database design query: combine tables or keep separate?

Question

I'm starting to design a database driven website.

I have two similar tables, GPU vendor and CPU vendor. Both have the same columns: id, vendor_name, vendor_url, vendor_bio.

Would you make separate tables or combine them, perhaps adding a type column?

I don't want my inexperience to hinder my design later on. Think first, do later! :)

Answer 1

If they are actually 'Vendor' in problem domain. I'll create a table 'VENDOR' and add column 'VENDOR_TYPE' along with your other columns.

Table Vendor{
    id, 
    vendor_type,
    vendor_name, 
    vendor_url, 
    vendor_bio    
}

But if they are two logically different Entity types, i would lean towards creating two separate tables. In your case it seems to be 'VENDOR' rather than 'GPUVendor' and 'CPUVendor'

Answer 2

YAU outlined the answer. In most cases like yours, I have combined the two tables into one, with a type column to indicate which type each entry belongs to.

It depends on how you will use the data. If many of your queries will combine rows from GPU types with rows from CPU types, then the single table table will yield better results. If almost all your queries are exclusively about GPU types or exclusively about CPU types, then you'll get better results from the two table design.

Ultimately, this boils down to what YAU said: are they two entities or one entity.

I'm going to add that, either way, you can build one or two views that make the data look the other way. If you create a single VENDORS table, you can create a CPU_VENDORS view that only selects the CPU types and a GPU_VENDORS view that only selects the GPU types. You can then access those views when convenient.

If you adopt a two table design, you can create a single VENDORS view that is the UNION of GPU_VENDORS and CPU_VENDORS, and use that when needed.

Except for performance effects, you'll get the best of both worlds out of the views.