Sql-server – Database design for Magazine website

Your self-reference could introduce a hierarchy of divisions within divisions. You need code (usually a trigger) to ensure that no divisions allow this.

The original schema using encoding is broken too. You have no enforceable FK

Personally, I'd consider this with a check constraint to ensure than only one of DepartmentID and DivisionID per row is populated

Article
-------
ArticleID (PK) int NOT NULL
DepartmentID (FK) int NULL
DivisionID (FK) int NULL
ArticleName varchar(50) NOT NULL

Why?

• no code needed
• no nested divisions
• full DRI via FKs to parent table
• enforced "multiple parent, only one at a time"

You could also use computed columns to add a column that says "department" or "division"

Another way: mandate that each department has at least one division...

If you know most of the attributes your products will have ahead of time, you could hard-code them into the tables for different product types, such as:

books
-----
  id
  price
  title
  author
  isbn
  etc....

mobile_device
-------------
  id
  price
  size
  colour
  model
  etc...

You could also try something like this:

base_product
------------
  id
  base_price
  product_type

book_product_attributes
-----------------------
  base_product_id (FK to base_product)
  title
  author

mobile_dev_product_attributes
-----------------------------
  base_product_id (FK to base_product)
  model
  colour

This way, you can group your common attributes in a base product table, and more specific attributes are stored in other tables when necessary.

This will work if your product types are mostly static. On the other hand, if you have no idea what product types or attributes you'll need in the future, and entity-attribute-value based system might work better. The idea behind that model is that you have a table for the different attributes you might have, a table for the different entities in your system, and a table for the attribute values that an entity has. Example:

entities
--------
  id

attributes
----------
  id
  name

values
------
  id
  entity_id
  attribute_id
  value

The data might look like this:

entities
ID
----
  1

attributes
ID |  name   
----------
1  |  title
2  |  author


values
ID  | attribute_id   | entity_id   | value
---------------------------------------------------------
1   | 1              | 1           | "Great Expectations"
2   | 2              | 1           | "Charles Dickens"

This data very briefly describes a product which has two attributes: title, and author, with the valus "Great Expectations" and "Charles Dickens", respectively.

Be aware that using an EAV database can make queries very awkward. With enough data and the wrong model design, it's also possible to run into performance problems. The example I gave was very simple but more realistic designs tend to be more complicated than that. It can take time to get this kind of database correct and it's not always the best solution.