5

I am building a website for users to be able to make their own profile page with details about themselves, users shall also be able to create pages (similar to those on G+ & FB), however, they can either associate their current personal account with their page or create a separate page with a different account. There shall be many different pages (page types).

I've found this picture:


(source: tomtenthij.nl)

Which seems very interesting, however, how can I structure this in a better way, for example: College Page, or Collect & University Page, or University Page or Hospital Page or Company Page etc. Each have their own set of attributes etc. Any help would be appreciated. I was thinking if Page_type could help me separate each depending on if it is a Hospital or Company etc.

EDIT: I am a beginner in Database Schemas, I have no problem with the PHP code. But want to know of a better way to structure a Database schema for multiple users, pages etc.

Improve this question
4
  • 1
    Systems like Facebook and Google+ generally use a graph database for this sort of data. Commented Feb 2, 2014 at 14:12
  • 1
    How do you mean? A graph database? What if I do not have that here in my home. I have mysql setup with PHP5 running in localhost here on my dev machine. Commented Feb 2, 2014 at 14:15
  • read up on Table Inheritance. You're on the right track. Adding "page_type" would be an example of Single Table Inheritance. Commented Feb 3, 2014 at 21:24
  • 1
    Neil, thanks for your reply. That's no longer the actual issue here. My issue is the account part and page or user profile page. Account part= e-mail, username, password (and a set of more details). Commented Feb 4, 2014 at 8:04

4 Answers 4

Reset to default
3

I had a cursory look on the tables. I found it satisfactory + problematic (or just I'm not understanding right away). It shall be better if we go through all the tables one by one and discuss the things over the matter of days. One week's input and roll in, roll out of ideas can give us good confidence on what we are going to achieve. Right?

First of all I would like to identify all the possible relations between a user and profile.

Secondly, I have to decide which will be my master table which will go on to occupy the relations with rest of the tables. Tables those are in or will come in the future. Don't forget that we have to put such a design in place which will never mind any kind of extension.

I read the comment about the graph database. Don't mind that is not something we are ready in position to dive in there. You are right on the track, just need to polish ideas. I'm starting this discussion so that other people can also join here.

I often ran out of time, but will try my best to discuss things as much as my knowledge can take me that far.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Superman, thanks for all of your help. I am ready to spend days on this forum to see what we can achieve. My worries are: Pages vs. Standard Users. A user is different from a Page, a Page can be a company, hospital, school, organization, government agency etc.
Superman, I also found this now: socialmeteor.com/wp-content/uploads/2009/05/facebook-schema.pdf - it's interesting, however, very misleading when it comes to pages.
I am thinking on your pages (business, celebrity, hospitals etc) these things can help us but it is not a good idea to get a schema and start molding our problem according to it. So bear with me, I'm not sure about other users who will contribute here. But bear with me, I'll propose you a solution about this in the morning. I hope this Sunday to next Sunday we will have something working in front of us. :) Don't worry, You already committed to give it sometime. Off to bed now will think something good in my dream for your problem. Have a good day. And let me bookmark this post.
I see. It's OK, the thing has to do with attributes, but also that pages and user profile pages share on thing in common, account information: e-mail, password etc. I will wait until tomorrow and continue to search around the Internet. Thanks a lot Superman!
I am looking forward!
|
1

I think @Uours is on the right page but I would suggest a slightly different approach which would allow you to define attributes once and then use them on your various pages. Something like:

page_types page_type_id (PK) page_type_name (e.g. College, College & University, University, Hospital, Company, ...) page_attributes page_attribute_id (PK) page_attribute_name (e.g. Name, Address, City, Country, Email, ...) page_attribute_input_type (e.g. text, checkbox, date, email, password, radio, textarea, select, ...) page_attribute_input_options (e.g. list of values, size=, maxlength=, pattern=, ...) page_type_attributes page_id (PK, FK) page_attribute_id (PK, FK) page_attribute_sequence (PK) page_type_attribute_label (defaults to page_attribute_name but can be overriden in case the same attribute is used more than once on a page) users user_id (PK) user_name ... user_pages user_page_id (PK) user_id (FK) user_page_type_id (FK) user_page_name (What the user calls this page) user_page_attributes user_page_attribute_id (PK) user_id (FK) user_page_id (FK) page_attribute_id (FK) user_page_attribute_value

Good luck. Hope this helps.

Improve this answer

Comments

1

If I understood your requirement correctly , I suppose this could be rough design of one way to implement User Pages :

user user_id user__name user__password user__email ... ... page_type ( Holds Page Ids and Names of various Page Types ) page_type_id page_type__name ( Ex: College , University , Company , ... ) page_attribute ( Holds the Structure/Attributes of various Page Types ) page_attribute_id page_attribute__page_type_id page_attribute__number page_attribute__order page_attribute__input_type ( Ex: 1: Text Line , 2: Text Area , 3: Drop Down List , ... ) page_attribute__options ( Ex: For input_type having multiple options , specify the list here as CSV ) user_page ( Holds Page Data for each Page for each User ) user_page_id user_page__user_id user_page__page_attribute_id user_page__page_attribute_data
Improve this answer

Comments

1

I've made a diagram of what i think the way to go. So here is it, the explaination & sql follow :

enter image description here

Explaination :

  • Account : store connection credential. Say you accept facebook login or twitter login, you must associate each credential to the same profil. So a user can login with 3 or more sort of account per profil.
  • page : Here you put every basic information about a page (whatever her type)

  • page_extrafield : here is the magic. In your PHP when a user choose 'Create a University page', you display the appropriate form with some fields. Then you save into the database only the field the user has filled. Like :

    • field_name = 'Established'
    • filed_value = '1909'

    and for an enterprise page, the php form could send to sql :

    • field_name = 'CEO'
    • filed_value = 'Steve Jobs'

  • role : profil can(or not) create a page or multiple pages. And multiple user can manage a single page. So you need a role table to store each users role. Mine is basic like 'Admin','Editor' or 'Member' but you can go for more complicated !

So everything you have to do is a multiple form php with appropriate SQL. Here is the BDD I used for this example, if you want to test it by your own :

-- Généré le : Mar 18 Février 2014 à 16:50 SET FOREIGN_KEY_CHECKS=0; SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET AUTOCOMMIT=0; START TRANSACTION; -- -- Base de données: `socialnetwork` -- CREATE DATABASE `socialnetwork` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE `socialnetwork`; -- -------------------------------------------------------- -- -- Structure de la table `account` -- DROP TABLE IF EXISTS `account`; CREATE TABLE IF NOT EXISTS `account` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(250) NOT NULL, `password` varchar(50) NOT NULL, `credentialtype` enum('Email','Facebook','GPlus','Twitter') NOT NULL DEFAULT 'Email', `profil_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `profil_id` (`profil_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; -- -- Contenu de la table `account` -- INSERT INTO `account` (`id`, `username`, `password`, `credentialtype`, `profil_id`) VALUES (1, '[email protected]', '*7AD0EBD5D5AF7AFF797419070CAEAF6A7671328A', 'Email', 1), (2, '1197666810', '', 'Facebook', 1); -- -------------------------------------------------------- -- -- Structure de la table `page` -- DROP TABLE IF EXISTS `page`; CREATE TABLE IF NOT EXISTS `page` ( `id` int(11) NOT NULL AUTO_INCREMENT, `type` varchar(50) NOT NULL DEFAULT 'Page' COMMENT 'type of the page ( university, College,etc)', `name` varchar(200) NOT NULL COMMENT 'Page Name', `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Store general data about page ( common to each page)' AUTO_INCREMENT=3 ; -- -- Contenu de la table `page` -- INSERT INTO `page` (`id`, `type`, `name`, `created_at`) VALUES (1, 'UniversityPage', 'Oxford Official Page', '2014-02-18 16:41:12'), (2, 'CompagnyPage', 'My Little Compagny', '2014-02-18 16:41:12'); -- -------------------------------------------------------- -- -- Structure de la table `page_extrafield` -- DROP TABLE IF EXISTS `page_extrafield`; CREATE TABLE IF NOT EXISTS `page_extrafield` ( `id` int(11) NOT NULL AUTO_INCREMENT, `field_name` varchar(100) NOT NULL COMMENT 'name of the specific field (like ''location'' or ''logo'')', `filked_value` text NOT NULL, `page_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `page_id` (`page_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Store every specialized field about the page (depend on the type of the page)' AUTO_INCREMENT=15 ; -- -- Contenu de la table `page_extrafield` -- INSERT INTO `page_extrafield` (`id`, `field_name`, `filked_value`, `page_id`) VALUES (8, 'location', 'https://maps.google.com/maps?f=q&source=s_q&hl=fr&geocode=&q=oxford&sll=37.0625,-95.677068&sspn=61.323728,135.263672&vpsrc=0&t=h&ie=UTF8&hq=&hnear=Oxford,+Royaume-Uni&z=13&iwloc=A', 1), (9, 'established', '1909', 1), (10, 'Enrollment', '499 students', 1), (11, 'Headmaster', 'Dennis Bisgaard', 1), (12, 'logo', 'http://foo.com/logo.png', 2), (13, 'Headquarters', 'San Fransisco', 2), (14, 'CEO', 'John Doe', 2); -- -------------------------------------------------------- -- -- Structure de la table `profil` -- DROP TABLE IF EXISTS `profil`; CREATE TABLE IF NOT EXISTS `profil` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pseudo` varchar(150) NOT NULL, `name` varchar(100) NOT NULL, `forname` varchar(100) NOT NULL, `picture_url` varchar(500) NOT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ; -- -- Contenu de la table `profil` -- INSERT INTO `profil` (`id`, `pseudo`, `name`, `forname`, `picture_url`, `created_at`) VALUES (1, 'Jedy', 'Luke', 'Skywalker', 'pict1.jpg', '2014-02-18 16:39:06'); -- -------------------------------------------------------- -- -- Structure de la table `role` -- DROP TABLE IF EXISTS `role`; CREATE TABLE IF NOT EXISTS `role` ( `profil_id` int(11) NOT NULL, `page_id` int(11) NOT NULL, `access` enum('Admin','Editor','Member') NOT NULL DEFAULT 'Member', PRIMARY KEY (`profil_id`,`page_id`), KEY `page_id` (`page_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Contenu de la table `role` -- INSERT INTO `role` (`profil_id`, `page_id`, `access`) VALUES (1, 1, 'Admin'), (1, 2, 'Editor'); -- -- Contraintes pour les tables exportées -- -- -- Contraintes pour la table `account` -- ALTER TABLE `account` ADD CONSTRAINT `account_ibfk_1` FOREIGN KEY (`profil_id`) REFERENCES `profil` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Contraintes pour la table `page_extrafield` -- ALTER TABLE `page_extrafield` ADD CONSTRAINT `page_extrafield_ibfk_1` FOREIGN KEY (`page_id`) REFERENCES `page` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Contraintes pour la table `role` -- ALTER TABLE `role` ADD CONSTRAINT `role_ibfk_2` FOREIGN KEY (`page_id`) REFERENCES `page` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `role_ibfk_1` FOREIGN KEY (`profil_id`) REFERENCES `profil` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; SET FOREIGN_KEY_CHECKS=1; COMMIT;

Have Fun and say if it works for you !

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.