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
- opening hours
museums have the variables
outdoor spots have the variables
Authorname is stored in a separate table with author related data
How is now best to organize the data?
- 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?
- 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)
- 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!