Jump to content
jodunno

bookmark table design

Recommended Posts

Hello SQL gurus,

details of system: xampp installation with MariaDB v10.1.30 with PHP v7.2.1

I have set up my website file system to include a bookmarking system. The foundation is intact now i need to set up the database to store the values of the bookmarks. click on my bookmark icon which will submit a bookmark profile block for storage in the database (a serialized array of necessary values). I want to limit the bookmarks to 100 entries per user.

i don't know exactly how to build this table and retrieve the data. is anyone able to offer design tips? i figure that i should make a database named bookmarks with a table for each user? then add an id row and a bookmark title (for duplicate detection) and the serialized profile for redisplay. I guess one can select all data in the table but how should i store each result for analysis and redisplay of the profile? i am not an sql guru. i currently only use simples select statements.

I hope that someone can offer some tips.

Thank you!

Share this post


Link to post
Share on other sites
28 minutes ago, jodunno said:

i figure that i should make a database named bookmarks with a table for each user?

No. One table named "bookmark" and store the user's id in each bookmark record. If your site currently uses a database, just put the extra table in that.

You can check how many each user has with a simple

SELECT COUNT(*) as total FROM bookmark WHERE userid = ?;

 

30 minutes ago, jodunno said:

serialized array of necessary values

How that is stored depends on what those "necessary values" are. If the arrays always contains the same keys I'd create a column in the bookmark table for each key. If the keys vary from bookmark to bookmark then store as a json encoded array (MySQL 5.7+ supports json columns)

If "bookmark_title" should be unique, enforce it in the table by placing a UNIQUE constraint on that column.

  • Like 1

Share this post


Link to post
Share on other sites

Hi Barand,

Thank you for the tips. You are very kind to take time to help a newb. I appreciate your post very much!

I've made notes from your post. I will use a table like you mentioned. I am not really new to php/sql but i am obviously not a programmer. I suck, actually. I have alot of trouble with database design. I have not mastered this concept. I made a login system three years ago for the first time in my programming tenure. So i have a dtabase of users. I will just add this table to this database like you mentioned.

Thank you for the count() function tip. I didn't realize that sql has this function. Very nice, indeed! and very helpful post from you.

i can explain a bit more while sticking to the bookmark db problem/topic: i actually built my own routing system which does not use query strings. I use forms and session variables to load pages through a single index file. So the "link" is a partial path to the php file system to load the correct "page" in a single page template. Somewhat spaghetti code but i do not like frameworks. I am happy for now. I will get better over time. Anyway, the bookmark will be a collection of known data from the template page:

page link as a db title to detect duplicates: path/to/data (path is always the root. bookmarks are only used within these pages. thus path is like c:/)

css icon display: i use icons to represent the links to pages and this is either a round icon or rectangular icon (which holds a photo).

icon photo link: each icon has a unique photo, so i need this data to get the correct icon for the bookmark.

language: i use a multi language file system so this is important to display the correct icon title by language. so here en-us, en-uk, de-de etc.

icon title: all ready present on the page as a variable ($pageTitle)

all of the above is my bookmark profile. now i just need to store each bookmark <101 into a database. Then figure out how to read these entries to display them on your bookmarks page (i guess a loop to read each entry and store each entry into an array?) I figure that serialized profiles will make this an easier task. How to know duplicate entries? i figure that a bookmark title (the page link) can be used. Thus, i can easily detect if this page is all ready bookmarked or not.

I will give it my best try and post any problems here. Remember, i am not a guru/programmer. Thus, i may need alot of time to get this set up. Your tips will help me.

Thank you!

Share this post


Link to post
Share on other sites

By the way, i have to read about json encoded arrays. is this serialized like php serialize()? i'm not familiar with this so i will research json encode/decode. Thanks for he tip!

Share this post


Link to post
Share on other sites

I don't think you need bother with the json. Just define your table as something like this

CREATE TABLE `bookmark` (
  `bookmark_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `page_link` varchar(50) DEFAULT NULL,
  `css_icon` varchar(50) DEFAULT NULL,
  `icon_photo_link` varchar(50) DEFAULT NULL,
  `language` varchar(10) DEFAULT NULL,
  `icon_title` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`bookmark_id`),
  UNIQUE KEY `unq_page_link` (`page_link`),
  KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

  • Like 1

Share this post


Link to post
Share on other sites

Hi Barand,

Amazing code and you only read a description of my bookmark profile. You are a 'helluva' coder. Your expertise and mastery shows in your replies.

I don't really need to change anything that you have posted other than names but i didn't post to get free code. I am trying to learn from your example. I'm reading about sql now so that i can think better about these problems and approprite solutions. I'd like to come to the same conclusions as you  oneday. I really learn alot from you and i thank you for that.

Meantime, i've changed the last login code and it works well. I was actuly just inserting your login into lastlogin then inserting the current login into current login. I guess it is easier to say that login becomes your last login before i update the login. I guess i was thinking wrong here. Your idea is better.

I don't have time to add the book mark code today. I have alot of things to do and i am behind schedule. I'll read more about sql before i go to bed, then tomorrow i will tackle this topic. I finished adding the bookmark profile to each page, so all i have to do now is submit it to the dbase. This code example is a great start! I also have to read the data from the db before i can display the bookmarks. I do not have so much coding experience as you do, so i am a bit slow. I'll update the post when i can finish this feature. I'll let you see the final code here so you can offer an opinion if you want to do so.

Thank you, Barand, i have learned alot about sql today. You are steeringme in the right direction!

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.