Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2009
    Posts
    23

    Unanswered: What's the Best Table Structure for a Membership System?

    Hi,

    I've started to a new project and I will implement a membership system. I know it's not so hard to implement a membership system, but I'm trying to prepare the best table structure. My study is not related to any specific database system (like MySQL or MSSQL etc) but a generic membership structure.

    Most of the membership systems have a single "users" table with everything in it, like the one below;

    Code:
    CREATE TABLE `users` (
      `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
      `username` VARCHAR(20) NOT NULL DEFAULT '',
      `password` VARCHAR(20) NOT NULL DEFAULT '',
      `email` VARCHAR(100) NOT NULL DEFAULT '',
      `birthdate` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
      `hobbies` VARCHAR(500) NOT NULL DEFAULT '',
      `favorite_music` VARCHAR(500) NOT NULL DEFAULT '',
      `favorite_book` VARCHAR(500) NOT NULL DEFAULT '',
      `favorite_tv_shows` VARCHAR(500) NOT NULL DEFAULT '',
      `favorite_quotes` VARCHAR(500) NOT NULL DEFAULT '',
      `marital_status` TINYINT(4) NOT NULL DEFAULT 0,
      `educational_status` TINYINT(4) NOT NULL DEFAULT 0,
      `created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
      PRIMARY KEY (`id`)
    )
    Fields are not so important as you can add so many other extra fields. It's so easy to query this table. If you want to get all the information about a user, all you have to do is to select user's row;

    Code:
    SELECT * FROM users WHERE username = 'XYZ'
    But what if lots of users don't fill most of the fields, like "marital_status", "hobbies", "birthdate" etc. Then is this logical to hold all those fields in one table?

    I thought of another table structure. User's required fields, like "username", "password" and "email" are held in "users" table where other info are held in a "users_meta" table;

    Code:
    CREATE TABLE `users` (
      `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
      `username` VARCHAR(20) NOT NULL DEFAULT '',
      `password` VARCHAR(20) NOT NULL DEFAULT '',
      `email` VARCHAR(100) NOT NULL DEFAULT '',
      `created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
      PRIMARY KEY (`id`)
    )
    
    CREATE TABLE `users_meta` (
      `meta_id` INTEGER(11) NOT NULL AUTO_INCREMENT,
      `user_id` INTEGER(11) NOT NULL DEFAULT 0,
      `birthdate` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
      `hobbies` VARCHAR(500) NOT NULL DEFAULT '',
      `favorite_music` VARCHAR(500) NOT NULL DEFAULT '',
      `favorite_book` VARCHAR(500) NOT NULL DEFAULT '',
      `favorite_tv_shows` VARCHAR(500) NOT NULL DEFAULT '',
      `favorite_quotes` VARCHAR(500) NOT NULL DEFAULT '',
      `marital_status` TINYINT(4) NOT NULL DEFAULT 0,
      `educational_status` TINYINT(4) NOT NULL DEFAULT 0,
      `created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
      PRIMARY KEY (`id`)
    )
    Well, this seems more sensible than the one before. If most of the users don't want to fill extra info, then the "users" table will be so fast to query. But then another question arises; what if someone fills some info in the "users_meta" table but not everything. Maybe some users will fill only the "birthdate" field, some will fill "birthdate", "marital_status", "hobbies", some will fill everything etc. Then this table seems to become a little bit bloated too. Oh and btw, I really don't know how to write a SINGLE query to join those two tables.

    There comes another table structure in my mind;

    Code:
    CREATE TABLE `users` (
      `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
      `username` VARCHAR(20) NOT NULL DEFAULT '',
      `password` VARCHAR(20) NOT NULL DEFAULT '',
      `email` VARCHAR(100) NOT NULL DEFAULT '',
      `created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
      PRIMARY KEY (`id`)
    )
    
    CREATE TABLE `users_meta` (
      `meta_id` INTEGER(11) NOT NULL AUTO_INCREMENT,
      `user_id` INTEGER(11) NOT NULL DEFAULT 0,
      `meta_field_id` TINYINT(4) NOT NULL DEFAULT 0,
      `meta_value` VARCHAR(500) NOT NULL DEFAULT '',
      `created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
      PRIMARY KEY (`id`)
    )
    
    CREATE TABLE `users_meta_fields` (
      `meta_field_id` TINYINT(4) NOT NULL AUTO_INCREMENT,
      `meta` VARCHAR(100) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`)
    )
    Now this seems the most suitable table structure for me. All users fill required info into "users" table. Extra info goes into "users_meta" table, no matter how many fields you want to fill in. "users_meta.meta_field_id" is connected with "users_meta_fields.meta_field_id". This table is prefilled with all the possible meta types, like "hobbies", "marital_status", "educational_status" etc. If you want to insert some meta info, all you have to do is to insert them row by row. Some users will have one "meta" row, while others will have more than one. And I don't know how to join those 3 tables into a single query too

    To cut a long question short; what is the best table structure for a membership system? It's so important that the membership system has to operate so fast, as there may be thousands of registered users. So I'm looking for a high-performanced and reliable solution. If you choose one of the latest two examples that I've given, then I would be very happy if you please provide me a single query to join the tables, so that I can get a user's info in one single row.

    PS: I'm not sure about the category of this question as there is no "Database Structures" or "Database Basics" categories. If I'm posting this question into wrong forum, please forgive me

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by demods View Post
    To cut a long question short; what is the best table structure for a membership system?
    in my opinion, it is ~not~ the last one

    that's called an EAV (entity-attribute-value) scheme, and although it is deceptively simple in structure, it is ridiculously difficult to get meaningful information out of

    do a search for yourself to see

    the second scheme is not really any better than the first, because it's a one-to-one relationship

    by the way, one of the things that's always puzzled me is why someone would design a table with key information defined as follows:
    Code:
    `username` VARCHAR(20) NOT NULL DEFAULT '',
    `password` VARCHAR(20) NOT NULL DEFAULT '',
    are you seriously going to accept a new user who registers without a username or password?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Posts
    23
    Quote Originally Posted by r937 View Post
    in my opinion, it is ~not~ the last one
    Well maybe I have to tell more why I thought that the last one seemed more sensible to me. Let's say that we choose the first table structure. What if we have to add extra fields that don't exist in the table? We will have to change the "users" table structure while there are lots of users registered users. This didn't seem sensible to me, but of course I would love to hear your solution for adding extra fields.

    that's called an EAV (entity-attribute-value) scheme, and although it is deceptively simple in structure, it is ridiculously difficult to get meaningful information out of

    do a search for yourself to see
    I really didn't know that. I will definitely do a search

    the second scheme is not really any better than the first, because it's a one-to-one relationship
    So if the last one is not sensible and the second one doesn't make any sense, then do you suggest to use the first one?

    by the way, one of the things that's always puzzled me is why someone would design a table with key information defined as follows:
    Code:
    `username` VARCHAR(20) NOT NULL DEFAULT '',
    `password` VARCHAR(20) NOT NULL DEFAULT '',
    are you seriously going to accept a new user who registers without a username or password?
    No, of course not. I prepared those tables so fast to ask my question. Please don't mind those errors as this is not the root of my question

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by demods View Post
    I would love to hear your solution for adding extra fields.
    ALTER TABLE tablename ADD COLUMN ...

    let me know when you've finished researching EAV

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2009
    Posts
    23
    Quote Originally Posted by r937 View Post
    ALTER TABLE tablename ADD COLUMN ...

    let me know when you've finished researching EAV

    I've read more than 10 articles about EAV and I've realized that EAV is not the solution. Evil EAV Thanks for helping me to realize this.

    BUT, I'm still looking for the best solution. OK, let's throw away those 2 nonsense table structures and concentrate on the first one. Does it make sense to use the first one? I still have some fears/suspicions about that table structure.

    • What will happen when lots of users pass some fields as "NULL"? Would this really be a bad database design?
    • If I ALTER the table and ADD COLUMN, how does this affect the performance of a site with hundreds of thousand of users?
    • All records will have NULL for this newly added column. Is this bad?
    • I saw that some clinical and e-commerce applications use EAV model. What would they do instead of EAV?

    EAV is evil, I'm searching the angel

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by demods View Post
    What will happen when lots of users pass some fields as "NULL"? Would this really be a bad database design?
    in my opinion, no... provided that the data is "optional"

    obviously you're not going to want to allow NULL for important columns like username or password


    Quote Originally Posted by demods View Post
    If I ALTER the table and ADD COLUMN, how does this affect the performance of a site with hundreds of thousand of users?
    it must be accomplished with the table locked, i.e. temporarily unavailable

    Quote Originally Posted by demods View Post
    All records will have NULL for this newly added column. Is this bad?
    you may optionally set a default, e.g.
    Code:
    ALTER TABLE users 
    ADD COLUMN favourite_database VARCHAR(37) NOT NULL DEFAULT 'mysql'
    Quote Originally Posted by demods View Post
    I saw that some clinical and e-commerce applications use EAV model.
    clinical apps are the only ones which can get away with it, they really have very little choice

    e-commerce apps which use EAV are notoriously horrible -- i have not used one myself, but i've seen plenty of people posting truly ugly queries when asking for assistance on forums like this...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2009
    Posts
    23
    I'm starting to get the point. But new questions start to arose in my mind.

    Let's take a social website like Facebook. If you edit your personal information, you will see there are lots of information to edit. When you start to edit your "Education and Work" info, you will see that you can add as many "Job Information" as you like. No matter how many you add, you have the chance to add infinite (or maybe finite??) job information. So, if you don't use EAV model, what would you use to implement such a feature? I'm sure Facebook don't temporarily become unavailable when they add a new field for the users.

    Facebook have more than hundreds of fields to edit. If I can understand the model that websites like Facebook use, I'm sure I will find a suitable solution.

    Btw, your answers are always perfect guides for me. I really learn so much, so thanks a lot.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by r937 View Post
    e-commerce apps which use EAV are notoriously horrible -- i have not used one myself, but i've seen plenty of people posting truly ugly queries when asking for assistance on forums like this...
    If those "dynamic attributes" are mainly used for informational display, I more and more tend to store them in a XML column. Especially if the end user has to have the ability to add new attributes (which rules out an ALTER TABLE....)

    Yes, I am aware this has nothing to do with a proper normalized relational data model, but sometimes the relational model isn't the right choice

    If at some time there is is a requirement to do reporting on the attributes, the XML column can easily transformed into relational columns using XPath expressions and/or XQuery. If performance matters those columns then can be indexed as well.

    And I can have a different set of attributes for different rows (e.g. for different customers) which would not be possible when adding columns to the table if new attributes are needed

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by demods View Post
    If I can understand the model that websites like Facebook use, I'm sure I will find a suitable solution.
    are you sure you want to jump into something that complicated?

    Facebook database schema on Flickr - Photo Sharing!

    Cassandra ? A structured storage system on a P2P Network | Facebook
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Dec 2009
    Posts
    23
    are you sure you want to jump into something that complicated?

    Facebook database schema on Flickr - Photo Sharing!
    Yes, this is it. I can see the profile part and related tables and fields. And it's so easy to understand the structure. This photo will be my guide. Now I can sleep in peace.

    Thank you so much

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •