After reading a lot around and receiving a hint about SQL data normalization I came with this schema. The rest will be handled by simple requests:
- Table MRindex - All details about a entry will be listed here:
key db | user_made_id | description | date | blob_picture | rss_feed | twitter_alias/keyword/hashtag |
- Table MRtwitter - All twitter related data will be stored here:
key db| index-at-table1| tw_option|tw_user | tw_date | tw_location | tw_name |tw_text
- Table MRrss - All rss data will be stored here:
key db | index-at-table1| rss_post_author | rss_post_date | rss_post_title | rss_post_text
Any comments & hints would be more than welcomed :-)
EDIT: To clarify a bit...
MRIndex (also referred as table1) holds basic data, filled by the user: Might be user tweets, hashtags or location based tweets. Once this entry is filled up
MRtwitter will hold the tweets and related info. The field index-at-table1 is the "link" to table MRindex.
MRrss it's the the same for blog posts. Same thing applies for 'index-at-table1'.
For MRindex:
description: short user description
date: date of creation
blob_picture: a picture if any (jpg, png, etc)
rss_feed: An RSS_feed URL belonging to this user
twitter_alias/keyword/hashtag: either @user, Greece (for location) or #hashtag
You can change your post_order
column to a float
and update the value with a calculation using the post_order
values from the posts you want to end up between.
Example:
PostID, PostOrder
1 1
2 2
3 3
If you want to move PostID = 3 to be sorted between 1 and 2 it would be
PostID, PostOrder
1 1
3 1.5
2 2
Here is a SQL Fiddle with some code that moves the posts using a stored procedure where you pass in the PostID
of the post to be moved and PostID
of the post you want to end up behind.
SQL Fiddle
MySQL 5.5.30 Schema Setup:
create table Post
(
PostID int primary key,
PostOrder float unsigned not null unique,
check (PostOrder > 0)
);
insert into Post(PostID, PostOrder) values (1, 1);
insert into Post(PostID, PostOrder) values (2, 2);
insert into Post(PostID, PostOrder) values (3, 3);
insert into Post(PostID, PostOrder) values (4, 4);
insert into Post(PostID, PostOrder) values (5, 5);
insert into Post(PostID, PostOrder) values (6, 6);
insert into Post(PostID, PostOrder) values (7, 7);
insert into Post(PostID, PostOrder) values (8, 8);
insert into Post(PostID, PostOrder) values (9, 9);
insert into Post(PostID, PostOrder) values (10, 10);
//
create procedure MovePost(MovePostID int, AfterPostID int)
begin
declare AfterPostOrder float;
declare NextPostOrder float;
set AfterPostOrder = (select PostOrder
from Post
where PostID = AfterPostID);
if AfterPostOrder is null then
-- Move first
set AfterPostOrder = 0;
end if;
set NextPostOrder = (select min(PostOrder)
from Post
where PostOrder > AfterPostOrder);
if NextPostOrder is null then
-- Move last
set NextPostOrder = (select max(PostOrder) + 2
from Post);
end if;
update Post
set PostOrder = (AfterPostOrder + NextPostOrder) / 2
where PostID = MovePostID;
end
Query 1:
call MovePost (7, 3); -- Move 7 after 3
call MovePost (8, 3); -- Move 8 after 3
call MovePost (9, null); -- Move 9 first
call MovePost (2, 10); -- Move 2 after 10
select *
from Post
order by PostOrder
Results:
| POSTID | POSTORDER |
----------------------
| 9 | 0.5 |
| 1 | 1 |
| 3 | 3 |
| 8 | 3.25 |
| 7 | 3.5 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 10 | 10 |
| 2 | 11 |
Best Answer
No, adding a year table would add nothing to this data model.
Think about what a Year table would be like. What columns might it have? It really only has the year number itself - 2018, 2019 .. 2031, 2032 etc. Cards would then have a foreign key to this table, which the currently existing column
Cards.Year
already represents.If you were doing a logical data model you may choose to put the Year entity type in it. For a physical database implementation & table design there is no need to implement a Year table.
One caveat - if your years are not calendar years, and you care about the calendar dates that each membership year starts and end, then having a separate Year table makes sense. Think about the tax year. It starts in, say, calendar April and runs to the following calendar March. So tax year and calendar year do not cover the same set of days.