1

I have to store in database objects with different kind of properties. Also I want to group those objects in categories and the object can be part in multiple categories.

Example objects:

Book (ID, Title, Author, Description)

Movie (ID, Title, Author, Genre) 

Song (ID, Title, Singer, Year)

So far I have tried two methods:

Method 1

Create a Category table with fields ( ID, CategoryName).

Create a general table and try to match table fields with object properties. Join the category with objects in a helper table 

_CategoyObject_ (_CategoryID_, _ObjectID_).

This seems to be a bad practice because we have many columns with null values in general table. Also referring to the first point in this article it is a bad practice to store different objects in the same table.

Method 2

Create a Category table with fields ( ID, CategoryName).

Create table for each of the object.

Create a helper table to link the categories table with the name of the object table:

 _CategoyObject_ (_CategoryID_, *Object_Table_Name*)

This method can help us find the name of the table to create the join if we want to take objects for a given category.

But this complicates the query because we should have one query to get the tables for a certain category and another query to get records of objects

This is even harder because I am currently using Entity Framework code first to create the database.

Is it better to get the data with ADO.NET instead of Entity Framework for easier data retrieval?

Can you suggest me any other method that is simpler for the questions:

Get all the objects who belongs to X category?

Get in which category Y object belongs?

Gesi Kadiu
  • 11
  • 4
  • The category belongs to the table, not to the records of the table @TimSchmelter – Gesi Kadiu Aug 31 '18 at 13:23
  • Books, Movies and Songs are just for a simple example. The concrete objects doesn't have categories by itself. So the category is only for the table. – Gesi Kadiu Aug 31 '18 at 13:28
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Aug 31 '18 at 17:39
  • Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. See the downvote arrow mouseover text. – philipxy Aug 31 '18 at 17:40
  • Create a general table and try to match table fields with object properties and You are going to define the name of the column or property and you can pick a common name to maintain the field . For example Author - You can maintain the author of the book , author of the movie and singer of the song . Its like you can provide common name for similar filed types . By which you can reduce the null columns . but make sure you can document the same for future understanding. Thats in DAO layer. While send to UI you can map the relevant Class with fields and map based on category. – Abdul Sep 03 '18 at 07:39

1 Answers1

0

Create three tables, and call them movies, books, and songs. In this way, you will not need to look up the name of the table. When you need the books, you'll "select * from books".

Gerard H. Pille
  • 2,528
  • 1
  • 13
  • 17
  • It's not that simple. Books and movies belong to a category and movie and songs belong to another category. So i want to find records for one category. – Gesi Kadiu Aug 31 '18 at 13:26
  • Can you give me a couple of examples of such categories? – Gerard H. Pille Aug 31 '18 at 13:28
  • Lest say for example I have a category Entertainment in which are Movies and Songs and another category Science with Books and Movies that belongs to it (all books and all movies belongs to it). – Gesi Kadiu Aug 31 '18 at 13:34
  • Then you would indeed need a category table, plus, for each of books, movies and songs, a relation table linking books to categories, movies to categories and songs to categories. That way you'll establish a many-to-many relationship between categories and the other entities. – Gerard H. Pille Aug 31 '18 at 13:40