Database design to store different entities with different variables

Databases
1

Hello!

I am very new to database design and currently read how to organize data best (e.g. many-to-one, one-to-one relationships) Therefore, I would be glad if you can help to organize the following data best.

I like to store information about places (restaurants, museums and outdoor spots)

restaurants have the variables

  1. description
  2. mapdirection
  3. opening hours
  4. restaurantcategory
  5. authorname

museums have the variables

  1. description
  2. mapdirection
  3. openinghours
  4. museumcategory
  5. authorname

outdoor spots have the variables

  1. description
  2. mapdirection
  3. bestviewtime
  4. outdoorcategory
  5. authorname

Authorname is stored in a separate table with author related data

How is now best to organize the data?

  1. Create one table with all the information in it. But if I do this some places will have the entry “null” in some variables. E.g. outdoor spots will have no entry in the column “openinghours”
  • I though this is not good?
  1. Create one table named “places” with the variables “description” and “mapdirection” since these variables apply to all and include an additional variable “category” to identify whether it is a e.g. a restaurant or a museum. Additionally I would create a separate table for all other variables. E.g. openinghours and refer via the “id” column to another tables and use “INNER JOIN” commands to retrieve data.

But then I have lots of “INNER JOIN” to use which might be confusing. Especially if I have two one-to-many relationships (e.g. placecategory and restaurant category)

  1. I store all the information for each place in a different table. E.g. a separate table for restaurants, museums and outdoor spots.

But this might jam up select command as if I retrieve data of places in general.

I would be glad if you can give the best solution (or commonly used solution) how to organize such complex data)

Thanks in advance!

2

most common solution is probably #1

best solution is #2

do a search on “supertype/subtype”

3

Hi r937,

thanks for your answer and search tip. There is really a whole lot to read on relational database design and supertypes/ subtypes.

If you know a good summary of it, I am glad if you can provide me with a link or reading tip.

Thanks

4

http://www.siue.edu/~dbock/cmis450/4-eermodel.htm