Member plus application concept

[bach]

Hi,

I have been thinking of writing an web application for a member based system. I have come up with an initial DB design and would like some feedback advise next step's etc as this is the first time I am doing this.

 

Concept

To hold information about its members and what roles they do, it needs to be simple, fast and functional.

There are many aspects of this system for future development for building information, availability information etc so this needs to be thought about in the initial stages so a re-design will not be necessary.

 

Thanks.

[Liquid Fire]

WARNING: This is my personal preference.

 

One this i would say about your database naming convention is the way you name primary/foreign keys seems a little weird to me.  For primary keys, i think it is cleaner to use a standard name globally like id.  For primary keys i think it is cleaner to use the singular versions if the table name is plural(so the foreign key to the users table would be user_id) becuase something like u_id is going to be confusing and you will run into conflict is you have another table that begins with u and it one word and you need a foreign key to both another table.

[bach]

Liquid Fire,

Thank you for replying, I have amended the fields to reflect your comments.

 

[Liquid Fire]

Also i don't think you need house in the users table as street1/2/3 should do the trick.  Also is there a specific reason why you are storing the amended as an int instead of a datetime/timestamp?

[bach]

Liquid,

Thanks for the reply again, There is no specific reason for the data type of int for the amended date I saw this on another system and I was not sure which one was correct.  Do you know the difference between them.

Cheers.

[Liquid Fire]

well the int is going to store and give you the unix timestamp(or the number of second from 1970 or something) where and datatime/timestamp with return you the date like 2008-02-02 23:23:23.  I would recommend the datatime/timestamp as i find it easier to work.

[bach]

Thanks, I have changed this and added a further table.

 

Been thinking of the dbconnect, would you want to use one method for query checking members exist and another one to add information for security etc ?