yellow pages website database design ( take a look )

Question

I'm designing a yellow pages web site , i just finished the data base design and if you can i need some reviews about it.

country

ID, name, flagImage, creatDate

city

countryID, name, image, creatDate

companyType

id, city_id, name, image, description, creatDate

companySubType

id, company_type_id, name, image, description, creatDate

company

id, company_sub_type_id, name, logo_large, logo_small, description, established year, location, number of employees, creatDate

company_working_hours ( different opening hours for each day )

id, company_id, Sunday_open, Sunday_close, Monday_open, Monday_close, Tuesday_open, Tuesday_close, Wednesday_open, Wednesday_close, Thursday_open, Thursday_close, Friday_open, Friday_close, Saturday_open, Saturday_close, creatDate

company_branch ( company can have many branches )

id, company_id, location, description, creatDate

branch_opening_hours ( different opening hours for each day )

id, company_branch_id, Sunday_open, Sunday_close, Monday_open, Monday_close, Tuesday_open, Tuesday_close, Wednesday_open, Wednesday_close, Thursday_open, Thursday_close, Friday_open, Friday_close, Saturday_open, Saturday_close, creatDate

companyPhone ( company can add more than one phone )

id, company_id, phone_type, phone_number, description, creatDate

companyWebsite ( company can add more than one website )

id, company_id, website_url, description, creatDate

companyE-mail ( company can add more than one e-mail )

id, company_id, e-mail, description, creatDate

companyReview ( users reviews )

id, user_id, company_id, text, creatDate

companyRate ( users can rate the company )

id, user_id, company_id, rate_flag, creatDate

companyLike ( user can like the company )

id, user_id, company_id, like_flag, creatDate

companyPaymentMethod ( company can choose which payment method the accept )

id, company_id, credit_card, check, pay_pal, cash, debit, creatDate

SpecialServices ( like wi-fi, reservation, delivery ... ; i add the services in this table and link it with the company in other table )

id, service_name, service_phone_number, creatDate

companyServices

id, SpecialServices_id, company_id, creatDate

companyProduct

id, company_id, name, description, image, price, creatDate

companyAlbum

id, company_id, name, description, cover_image, creatDate

albumPic

id, companyAlbum_id, image_small, image_large, caption, description, creatDate

picComment

id, user_id, albumPic_id, message, creatDate

picLike

id, user_id, albumPic_id, likeFlag, creatDate

i was thinking to make a different data base for each country , what do you think about that ? thnx in advance :D

Answer 1

Given that one has to make many assumptions about your model, as pointed out by Mike Sherrill, I would say the model looks pretty good overall. One area where you might get yourself in trouble is around the opening hours. It is strange that you created a table for this but then used an unnormalized column set. I can't see how you would have multiple rows per company or branch using this column set. In any case, I think you want the separate table but totally different column definitions for the opening hours.

Depending on what type of business it is and where you are, there may be more than one opening per day. I can think of restaurants, some doctors, and just about any business in certain countries as an example. Some places only open at meal times, while others close for lunch etc. Therefore instead of using a non-normalized schema (i.e.: Sunday_open, Sunday_close, Monday_open, Monday_close, ... etc.) you should use a schema like this:

Company_Working_Hours
( id,
  company_id,
  day_of_week,
  open_time,
  close_time
)

You could use the same structure for branches too. This allows you to say that the company opens on Tuesdays from 11:00 to 2:00 (for lunch for instance) and from 17:00 to 23:00 (for dinner). This design allows you the flexibility to have multiple openings per day and saves you having to enter nulls for days that the business is closed altogether.

One other comment I would make about your schema is that you have geography divided into country and city. Some countries are big and have multiple cities with the same name (e.g. Springfield, USA). You might want to interject a province/state/prefecture table in between city and country. For countries without subdivisions (rare) you can have a single entry in this intervening table. I have seen this done with a flag on the table to indicate whether to show or hide the record, in case you are worried about showing the intervening layer when it is just a dummy record.

As to your question about segregating databases for each country, I'm not sure why you would do that. Unless you expect to deploy different countries on different servers, each of which will get their own distinct web traffic, I can't see an advantage to segregating countries. It would be a nice feature to let users pick a country. Changing the country selection will be easy if all of your data is together and much harder if each country has its own database.