1 table with empty fields vs more tables

Databases
1

Company can have 3 different type of ads:
a) Free which includes:
-text, image
b) Silver package which includes:
-text, image, logotype
c) Golden which includes
-text, image, logotype, rightbar ad text, rightbar ad image

So I am choosing between tables
Ads
id | companyId | adType | expires | text | image | logotype | rightbarAdText | rightbarAdImage

or creating for each adType another table
freeAds
id | companyId | text | image
silverAds
id | companyId | text | image | logotype
boldenAds
id | companyId | text | image | logotype | rightbarAdText | rightbarAdImage

Which one do you think is it better? If I choose 2nd option than tables won’t have empty fields but in this case more queries will be involved because I need to use this in search results and also when showing a company page.

2

that’s called EAV (entity-attribute-value)

in general terms, it is a disaster trying to get information out of such a scheme in an efficient manner

of course, this is what the “NoSQL” schemas use, but the difference is, they don’t use SQL to get at it, and they have a bazillion computers working in parallel on it

for MySQL implementations, using SELECT queries, you are asking for a world of hurt

3

Ah, you mean a Web 3.0 design where the database can be changed dynamically over time to contain whatever data that the site needs. I haven’t come across many sites that acknowledge that they are using Web 3.0 as yet although I did come across one book that had a really good explanation of how it all works (just can’t remember which book it was at the moment).

4

If you will have separate table for each ad type, you will have pane when you will be altering them in future (and you will). You also should not use empty columns. You could use horizontal table for ad properties.

5

What is a horizontal table? Surely all tables consist of rows and columns and one of the rules for normalising databases is to remove repeating groups so that the only repetition of data is down a column (at least it is with the way rows and columns are normally used)

6

Horizontal table is the structure, when columns are presented in rows. Looks like:

product_id, property, value

so you do not need to create empty columns for absent properties.

7

First option – single table. That is unless you plan on more ad types. You could also use an inheritance based design:

ads

  • companies_id
  • text
  • image

silver_ads

  • logotype
  • ads_id

gold_ads

  • right_bar_text
  • right_bar_image
  • silver_ads_id
8

You should normalise the data and work out from that what tables you need and what fields each should contain. Usually going to third normal form will take care of everything as the other normalisations cover rare situations.

You should only undo normalisations in going from your logical to your physical database design if iit is essential to ensure that processing is not slowed too much.