Your ideas for this magazine-database that i have design ?

Question

Hi all;

It is my appreciate that writ your suggestion about this db that i have design for a magazine online:

-one article can belong to many categories.
-one article can have null or many photos.
-one article can have null or many tags.

Table: `tb_categories`
Columns:
`cat_id` (Primary Key)
`catname`
`parent_id`

.

Table: `tb_articles`
Columns:
`article_id`(Primary Key)
`title`
`subtitle`
`textbody`
`source_id`
`date`
`remark`

.

Table: `tb_sources`
Columns:
`source_id` (Primary Key)
`sourcename`

.

Table: `tb_photos`
Columns:
`photo_id` (Primary Key)
`photofilename`

.

Table: `tb_tags`
Columns:
`tag_id` (Primary Key)
`tagname`

.

Table: `tb_articles_photo`
Columns:
`article_id` (Foreign Key)
`photo_id` (Foreign Key)

.

Table: `tb_articles_tags`
Columns:
`article_id` (Foreign Key)
`tag_id` (Foreign Key)

.

`tb_articles_categories`
Columns:
`article_id` (Foreign Key)
`cat_id` (Foreign Key)

Answer 1

Your design looks pretty solid.

Make sure that columns which must not be NULL are constrained as NOT NULL.

Everybody has different ideas about these things, but my suggestions:

• Don't prefix table names with "tb_" - it's redundant. We know it's a table because it's being used places where tables get used, such as from clauses and insert or update statements.
• I prefer to name tables for what they represent. If an entity represents an article, then name the table "article." In other words, use singular names. Think about it this way - if I query the table and get one row back, what am I going to get? An article. If I were to get one row back that contained a collection of articles, I'd name the table articles. (However, there's no "right" way - plenty of people disagree and prefer to name their tables with plural names)
• You might consider not prefixing your ID columns with the table name too. We know it's an article_id because it's in the article table. However, here again, plenty of people disagree and don't want to see an "ID" column in every table.