User Accounts: Registration / Login #5

Open
MisterPhilip opened this Issue Sep 10, 2012 · 17 comments

Comments

Projects
None yet
5 participants
@MisterPhilip
Contributor

MisterPhilip commented Sep 10, 2012

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

This comment has been minimized.

Show comment
Hide comment
@premiso

premiso Oct 11, 2012

Contributor

I will take over this and the ACL.

Contributor

premiso commented Oct 11, 2012

I will take over this and the ACL.

@ghost ghost assigned premiso Oct 11, 2012

@premiso

This comment has been minimized.

Show comment
Hide comment
@premiso

premiso Oct 11, 2012

Contributor

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.

Contributor

premiso commented Oct 11, 2012

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

This comment has been minimized.

Show comment
Hide comment
@premiso

premiso Oct 11, 2012

Contributor

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

Contributor

premiso commented Oct 11, 2012

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

This comment has been minimized.

Show comment
Hide comment
@ignace

ignace Oct 11, 2012

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.

ignace commented Oct 11, 2012

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

This comment has been minimized.

Show comment
Hide comment
@MisterPhilip

MisterPhilip Oct 11, 2012

Contributor

+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.

Contributor

MisterPhilip commented Oct 11, 2012

+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

This comment has been minimized.

Show comment
Hide comment
@AdamBoxall

AdamBoxall Oct 11, 2012

Member

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.

Member

AdamBoxall commented Oct 11, 2012

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

This comment has been minimized.

Show comment
Hide comment
@premiso

premiso Oct 11, 2012

Contributor

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?

Contributor

premiso commented Oct 11, 2012

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

This comment has been minimized.

Show comment
Hide comment
@MisterPhilip

MisterPhilip Oct 11, 2012

Contributor

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.

Contributor

MisterPhilip commented Oct 11, 2012

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

This comment has been minimized.

Show comment
Hide comment
@gizmola

gizmola Oct 11, 2012

Member

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 #2#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.

Member

gizmola commented Oct 11, 2012

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 #2#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

This comment has been minimized.

Show comment
Hide comment
@ignace

ignace Oct 12, 2012

@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 commented Oct 12, 2012

@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

This comment has been minimized.

Show comment
Hide comment
@ignace

ignace Oct 12, 2012

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.

ignace commented Oct 12, 2012

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

This comment has been minimized.

Show comment
Hide comment
@AdamBoxall

AdamBoxall Oct 24, 2012

Member

@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.

Member

AdamBoxall commented Oct 24, 2012

@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

This comment has been minimized.

Show comment
Hide comment
@ignace

ignace Oct 24, 2012

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.

ignace commented Oct 24, 2012

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

This comment has been minimized.

Show comment
Hide comment
@premiso

premiso Oct 25, 2012

Contributor

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

Thanks.

Contributor

premiso commented Oct 25, 2012

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

Thanks.

@ignace

This comment has been minimized.

Show comment
Hide comment
@ignace

ignace Oct 25, 2012

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.

ignace commented Oct 25, 2012

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

This comment has been minimized.

Show comment
Hide comment
@premiso

premiso Oct 25, 2012

Contributor

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

Contributor

premiso commented Oct 25, 2012

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

@MisterPhilip

This comment has been minimized.

Show comment
Hide comment
@MisterPhilip

MisterPhilip Oct 29, 2012

Contributor

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.

Contributor

MisterPhilip commented Oct 29, 2012

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment