User Accounts: Registration / Login

[MisterPhilip]

Preface

The user accounts for the main website should be integrated with the forums user system, requiring the user to only log into the website once and be able both post on the forums and interact with the main website with a single account. With our choice in forum software being IPB, a lot of the features from the forum can be integrated with the main website such as the "about me" content, contact details, location details, avatar / photo, et cetera.

Registration

Registration and modification (such as passwords) of a user account can all take place within the forum without the requirement to duplicate functionality onto the main website.

Login

The user should be able to log into their account from both the forum and main website via IPB's API.

[premiso]

I will take over this and the ACL.

[premiso]

Refs Issue #2

An initial database table setup for the user table will be:

create table users ( id BIGINT NOT NULL UNIQUE, displayname VARCHAR(50) NOT NULL, PRIMARY KEY(id) );

Since we are implementing the forum login etc, that should be the basics that we need for the site, the rest can be fetched from the forum side code.

[premiso]

Looks like IPB API is pretty decent for reference, here is the IPSMembers API Link

http://community.invisionpower.com/resources/documentation/index.html/_/developer-resources/api-methods/ipsmember-r739

[ignace]

id BIGINT NOT NULL UNIQUE,
PRIMARY KEY (id)

That's kinda upsetting. Why the BIGINT? Wouldn't an INT UNSIGNED suffice? Does PHPFreaks really have (or in the near future, need) >4 billion members?

Also the UNIQUE and PRIMARY indexes on id seem awkward. Care to explain? It's the first time I see someone do this.

I would go for:

CREATE TABLE `users` (
  `usr_id` INT UNSIGNED NOT NULL PRIMARY KEY,
  `display_name` VARCHAR(64) NOT NULL
) ENGINE INNODB CHAR SET utf8 COLLATE utf8_general_bin;

Which adds prefixes to common field names with a table short code to avoid name conflicts when joining and tedious code like table.column as alias.

[MisterPhilip]

+1 for @ignace's suggestion. I don't think we'll ever hit that amount of users... at least not within my lifetime ;)

Only thing I'd change is have the prefix of the ID match the table name completely & singularize the table name (user_id column / user as the table name), but that is nitpicking.

[AdamBoxall]

Also I tend to not include the table name in the column name, otherwise you're constantly repeating yourself:

select user.user_id ...

Reads better as:

select user.id ...

Very minor thing though.

[premiso]

I am with AdamBoxall, I prefer to use the .id and have the tablename be the laboration.

Ignace, no real particular reason other than just me being weird (I usually don't have people to critize stuff I write) so it is nice actually having an environment where I can learn new and better ways to do stuff. To be honest, I never looked at the differences in BIGINT just that it was BIGGER :)

Glad to start some fires. But that now brings up another issue, database consistency, is there a "coding standard" we are going to adhere to when it comes to create items for the database tables / columns?

[MisterPhilip]

I don't think we have discussed any standards for the db. That should probably be decided on, perhaps better suited for discussion in issue #2.

[gizmola]

On Thu, Oct 11, 2012 at 1:07 PM, Philip Lawrence notifications@github.comwrote:

I don't think we have discussed any standards for the db. That should probably be decided on, perhaps better suited for discussion in issue #2https://github.com/PHPFreaks/site/issues/2 .

— Reply to this email directly or view it on GitHubhttps://github.com/PHPFreaks/site/issues/5#issuecomment-9355714.

In practical experience, rather than doing a database design in advance, it tends to be that the better way to work with doctrine2 is to create the entity files, and use the app/console helper to generate the tables. It will use its conventions for the naming of tables it creates to resolve many to many relationships, for example.

In short, regardless of the various preferences people have, we're better off just going with what works best with doctrine in terms of naming of attributes. However, the way doctrine works, the entity files actually control the way doctrine will link the entities together, so they can actually be named whatever we want, but it just makes working with them more difficult if we don't use the doctrine2 conventions. For example, in the repository class bultin when you want to use the default methods like find() or findBy() you pass the name of the actual key columns, so it would be better if they weren't aliased off some other system.

[ignace]

@AdamBoxall @premiso

select users.id as user_id, group.id as group_id
from users
join groups on groups.id = users.group_id

select usr_id, grp_id
from users
join groups using(grp_id)

OR for the bold:

select usr_id, grp_id
from users
natural join groups

I don't understand why you would choose user.id over simply usr_id.

[ignace]

Like @gizmola mentioned the DDD approach is to start out with developing your domain-model first and only when your entire domain model is complete you create your DB design.

The result is that you end up with a pure OO-model instead of a persistence driven design. From my own experience I can tell that it's quite hard to make that switch from thinking in tables and relations to thinking in ONLY objects.

[AdamBoxall]

@ignace DDD approach aside for a moment, the only thing I don't like about naming IDs like that is that there's no convention to it. How you would shorten article.id for example isn't necessarily I would do it, which would mean I have to pause dev and check the table structure. Only a minor inconvenience of course. Though when you're joining onto a relation table that has article_id, it starts to look a little odd, or you're forced to alias the tables to avoid weird ID columns like artcl_id or blg_id.

[ignace]

PK's and FK's share the same name so if the PK is named artcl_id it has the same name in all tables that reference it which allows you then to either use a NATURAL JOIN (not recommended) or USING instead of an ON-clause.

Sure, it is a little inconvenience for you to look up my shortname for article or blog, but how about finding the shortname of every programmer on this project? Sample:

Programmer #1:

SELECT blog.id as blg_id, article.id as artcl_id
FROM blog
JOIN article ON blog.id = article.blog_id

Programmer #2:

SELECT blog.id as blog_id, article.id as art_id
FROM blog
JOIN article ON blog.id = article.blog_id

Programmer #3:

SELECT blog.id as blog_id, article.id as article_id
FROM blog
JOIN article ON blog.id = article.blog_id

Programmer #4:

SELECT blog.id as b_id, article.id as a_id
FROM blog
JOIN article ON blog.id = article.blog_id

Avoid the need for/forbid aliasing. You don't want to be on that project.

[premiso]

@ignace @gizmola do you have a book you recommend for the DDD stuff, I am very interested in reading more about it.

Thanks.

[ignace]

Eric Evans' book pretty much is the standard. http://domaindrivendesign.org/books/evans_2003

There are also quite a few good resources available online concerning DDD, even in PHP, though DDD is aimed at those domains that are hardest to model, it is widely adopted and the terms Entity, Factory, Repository, and Service used in the broadest sense of the word.

[premiso]

Cool will pick up the ebook and start reading it :)

[MisterPhilip]

IPB3.4 now has IPS Connect as another way to handle login/logout/register/etc. I know @gizmola said he was sort of against the idea of using the API, but it is there if we need it.