Can you advide on my system design

[oo7ml]

I'm creating a new tracking system on my site to allow members to see new content posted by administrators (unread content)

I have two questions which are marked throughout this post and i would be extremely happy to hear your thoughts and opinions on both of them:


I'm planning on creating a new tracking table with the following fields:

id - primary key
track_id - each item (piece of content) will be assigned a track_id
user_id - each track_id will be assigned to multiple user_id’s
status - auto set to 0 this will change to 1 when the content is read
date - for future design, if i ever need to know when content was read


Every post created by an administrator will need to be tracked by the tracking table before the content in the admin form is uploaded to the appropriate table. So if i have 50 users, the system should create 50 new records in the tracking table using one track_id for each user_id - 1. is this the best way of achieving this system.

After this has been carried out, the content in the admin form can then finally be uploaded to the designated table along with inserting the track_id into this table as well (to establish a relationship between the two tables)

The reason i choose to carry out the tracking first is because i need to get a track_id first, so that i can insert it into the designated content table as well

2. How do i increment the track_id field in the tracking table when a new tracker is created. Obviously the id field will be set to auto increment but i can't set the track_id field to auto increment as there will be 50 (no. of users) records using the same track_id for each item posted.

And sorry, another question 3. Does all this make sense, is there a better way to create this system (without using third party software)

thanks in advance

[callumd]

Before you insert data in to your tracking table, you first need to generate what I will call a content_id, based on the new content that has been created. If new content will be sitting in its own table, the table would look something like this:

Content
content_id (primary key)
content

Once you insert new content in this table, you will have yourself a content_id. Use the content_id in the tracking table:

Tracking
id (primary key)
content_id
user_id
status
date

[oo7ml]

Hi, thanks for your reply

Your method would work fine if there was only one table to hold content.

However my site is quite big and has over 15 tables for content alone - news, sport, business, finance, etc...

That's why i need to have a track_id or content_id generated before i add the content to each content table, thanks for your reply and i look forward to hearing yours and any other suggestions, thanks again

[callumd]

hi oo7ml,

You may want to decide if having many different content tables is the way to go. A better way might be to have just one content table, but add another field:

Content
content_id (primary key)
content
content_type_id

You would then have a table specifying the different content types:

Content_Types
type_id
type (eg, Sport, News, Finance, etc).

However, if you're determined to have different types of content in their own respective tables, you'll still need to create the content first, and then somehow tell the tracking table which bit of content you want to refer to. To do this, you may need to add another field, content type, to the tracking table:

Tracking
id (primary key)
content_id
content_type (eg 'Sport', 'News', 'Weather', etc)
user_id
status

The content_type field will let the system know which table to go and grab the content from.

In terms of what is considered to be a more "pure" kind of database design, all content really should be in one table, with a content_type_id field to distinguish what kind of content it is. There is no design-based reason you would separate content in to different tables if their only difference is the content type. It would be like having two users tables, one for males, and one for females. Why would you do it? You wouldn't. You'd have one users table, with a field that specifies gender.

[NogDog]

I think I would only add a record to the tracking table when a user actually reads the post. The tracking table would then just need 3 fields: content_id, user_id, date (could be a timestamp type); with the primary key being on the combination of the content_id and user_id fields (which would prevent duplicate entries if a user goes back to read such a post again. The content table would then need to include some type of field to indicate whether it needs to be tracked in the tracking field so your code knows whether to insert a record in the tracking table when a user reads it.