Clever Geek Handbook

Relational SQL Database

If you could fully explain or point me aside, I would really appreciate it.

I am trying to create a database that stores businesses and list them as actions to be performed in the local scope. In a level form, I want to store them as:

Action category

  • Action subcategory

  • activity

  • Where is the activity category: Indoor, Outdoor, Arts ... etc

  • If the subcategory of activity would be: Aquarium, Museum, Parks ... etc.

  • Where activities will submit listings subcategories: Florida Aquarium, British Museum.

Let's say I used these 3 names as my tables, what would be the correct relationship? In the ActivitySubCategory table, I would create an FK with a relation to the ActivityCategoryID Pk? Do I create an FK in the Activity table that belongs to the ActivitySubCategoryID PK?

+3


source to share


4 answers


Since it seems likely that you will have many different relationships (for example, there are many subcategories for arts, but British Musum can be in art and indoors), you should add a Join or Link table that only contains the ids values ​​as from the action table and from the subactivity table. Remember to use both as a PC so you can only enter the combo every time.



+3


source


Does each activity have one and only one category? My suggestion is to create an Activity table and then a Category table with a ParentCategory column - this will be a self-conscious join.



If an action can have one and only one category, then a simple FK relation is required. If not, then you need a 3rd table as associative.

+2


source


You could create foreign keys as you intended:

  • FK from ActivitySubCategory to ActivityCategory PK
  • FK from Activity to ActivitySubCategory PK

However, this will limit your activity to only one subcategory. If you need to associate activities with multiple subcategories, then you must create a lookup table with foreign keys for both activities and ActivitySubCategories.

If you imagine more possible levels of categories, you can create one ActivityCategories table that has an FK for itself. This will be parent-child relationships between rows of the same table, so you can create an ActivityCategories.ParentKey that references ActivityCategories.PK. The top level ActivityCategory will have a NULL ParentKey.

Also, I recommend creating indexes on your foreign keys. For example, create in index on Activity.ActivitySubCategoryKey and on ActivitySubCategoryActivityCategoryKey. If you don't, deleting activity categories or subcategories can take a very long time.

+1


source


To expand on other answers and give an idea of ​​how I approach this issue.

The database structure is the last thing I think about. At the end of the day, RDMS is a data warehouse where everything is stored when you are not using it.

Start by thinking about the real world problem you are modeling. Real-world objects will be modeled as software objects - what properties are needed and what methods are needed for these objects to evolve. Once you have such a structure, the database schema will fall out naturally.

For your problem, you have 2 things in the real world; activities and categories. Ignoring their unique properties, consider how these objects interact.

activity

  • Is the activity always in a category?
  • Only one category?
  • Can he navigate between categories?
  • If so, do you need to know where he was?
  • And when?
  • Will it always be part of a subcategory, or can it be linked directly to a category?

Category

  • Will there ever be only 2 levels in the hierarchy, or can there be sub-sub-categories?
  • Will subcategories only be part of one category?
  • Can they navigate between categories?
  • If so, do you need to know where he was?
  • And when?
+1


source






More articles:


All Articles