11 Replies - 2046 Views - Last Post: 03 April 2014 - 10:58 AM

#1 dsnowdon   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 221
  • Joined: 18-January 12

The database of a website/application that uses accounts

Posted 30 March 2014 - 05:40 PM

Hi, I am currently working on a project that holds user accounts and has features similar to facebook's 'likes' system my problem is when is user logs on to the application, The way it is currently modeled the code will have to get data from multiple tables using the where userId=userid. Is this best practise as i can see problems in query time when their are a lot of users. the only other way i can see doing it is create a database for each user.

What are your opinions?
Does anyone have any good links or tutorials?

Any input is appreciated.
Thanks Dale

Is This A Good Question/Topic? 0
  • +

Replies To: The database of a website/application that uses accounts

#2 dsnowdon   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 221
  • Joined: 18-January 12

Re: The database of a website/application that uses accounts

Posted 30 March 2014 - 05:44 PM

Hi, I am currently working on a project that holds user accounts and has features similar to facebook's 'likes' system my problem is when is user logs on to the application, The way it is currently modeled the code will have to get data from multiple tables using the where userId=userid. Is this best practise as i can see problems in query time when their are a lot of users. the only other way i can see doing it is create a database for each user.

What are your opinions?
Does anyone have any good links or tutorials?

Any input is appreciated.
Thanks Dale
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw   User is offline

  • a lovely bit of linq
  • member icon

Reputation: 6891
  • View blog
  • Posts: 28,510
  • Joined: 12-December 12

Re: The database of a website/application that uses accounts

Posted 30 March 2014 - 05:56 PM

Do not create a database for each user :helpsmilie:. A database is capable of handling, perhaps, millions of records.

where userId=userid

This is what you need to use, and what makes your database highly efficient: only retrieving limited, and relevant, data for the particular user.

Quote

The way it is currently modeled the code will have to get data from multiple tables using the where userId=userid.

I hope I am misreading this: you don't have to retrieve data from multiple tables just to verify the user I hope?!




Check out the Tutorials link at the top of this page, there are many good tutorials in either the Databases section or within the section for a particular language.
Was This Post Helpful? 0
  • +
  • -

#4 Martyr2   User is offline

  • Programming Theoretician
  • member icon

Reputation: 5543
  • View blog
  • Posts: 14,544
  • Joined: 18-April 07

Re: The database of a website/application that uses accounts

Posted 30 March 2014 - 05:58 PM

Well it depends on the type of data each table holds. One table might be related to the user directly like their name, email, address etc. Another table might be for data related to a particular user but not directly. Like for instance if this was a game where the user acquires resources like gold, stone, wood etc I might have a "resources" table which ties back to the user account through their ID. Another table might be for private messages to the user where the columns are related to the message and again the message is tied back to the user ID that the message is for.

There are many times where these one-to-many relations may exist... even where one-to-one relations exist if the two tables are tracking very different things but they are related to one another.

As for links or tutorials, I am not sure there are ones that are going to speak directly to your specific database design, but what you might want to look at are tutorials talking about relationships or "cardinality". Those tutorials can then show you how data is related to one another and then you might get a better idea why your database design is setup the way it is.

:)

This post has been edited by Martyr2: 30 March 2014 - 06:00 PM

Was This Post Helpful? 0
  • +
  • -

#5 dsnowdon   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 221
  • Joined: 18-January 12

Re: The database of a website/application that uses accounts

Posted 30 March 2014 - 06:02 PM

Martyr2 thanks for the input, so you would use tables, is their no query time disadvantages when their are lots of users in a table. For example a user looks at a page and the data needed is fetched from 5 separate tables using a WHERE userid=userid, what about performance considerations.
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is offline

  • a lovely bit of linq
  • member icon

Reputation: 6891
  • View blog
  • Posts: 28,510
  • Joined: 12-December 12

Re: The database of a website/application that uses accounts

Posted 30 March 2014 - 06:11 PM

Please do not create duplicate topics. Topics merged.
Was This Post Helpful? 0
  • +
  • -

#7 dsnowdon   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 221
  • Joined: 18-January 12

Re: The database of a website/application that uses accounts

Posted 30 March 2014 - 06:32 PM

View Postandrewsw, on 31 March 2014 - 01:11 AM, said:

Please do not create duplicate topics. Topics merged.


Sorry it wasn't on purpose 8|
Was This Post Helpful? 0
  • +
  • -

#8 Martyr2   User is offline

  • Programming Theoretician
  • member icon

Reputation: 5543
  • View blog
  • Posts: 14,544
  • Joined: 18-April 07

Re: The database of a website/application that uses accounts

Posted 30 March 2014 - 08:22 PM

View PostDkSnowdon, on 30 March 2014 - 06:02 PM, said:

Martyr2 thanks for the input, so you would use tables, is their no query time disadvantages when their are lots of users in a table. For example a user looks at a page and the data needed is fetched from 5 separate tables using a WHERE userid=userid, what about performance considerations.



As andrew pointed out, databases are more than capable of handling millions of records quite nicely. But if you are finding yourself pulling from 5 tables at a time to make a simple query, you should really look at the structure of your data in the database rather than the database itself. Not that it is unheard of, after all 6th normal form is essentially all lookup tables and some companies do use that form, but I hardly find myself ever going beyond 3 tables at any one time.

If you follow these few steps you should stay out of most performance bottlenecks...

1) Pull only the data you need. In other words only the columns you need, and limit the number of records you pull at a time.

2) Don't store columns which you could have calculated using other columns.

3) Reduce redundancy of data and make sure that the data is properly indexed.

4) Find a balance between data granularity and query efficiency. Meaning if you break your data into more and more tables, your queries are going to get more complex. If you clump all your data your queries may be less complex but may also be radically slower.

It is all about finding a balance. But all we can really say is that it depends on the data and what you are modeling to know which methods you should use.
Was This Post Helpful? 1
  • +
  • -

#9 dsnowdon   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 221
  • Joined: 18-January 12

Re: The database of a website/application that uses accounts

Posted 30 March 2014 - 08:25 PM

View Postandrewsw, on 31 March 2014 - 12:56 AM, said:

Quote

The way it is currently modeled the code will have to get data from multiple tables using the where userId=userid.

I hope I am misreading this: you don't have to retrieve data from multiple tables just to verify the user I hope?!


Thanks, It does not take data from multiple tables to verify or log on just in some pages I must store information fetched from multiples tables.

This post has been edited by andrewsw: 30 March 2014 - 08:27 PM
Reason for edit:: Reduced quote

Was This Post Helpful? 0
  • +
  • -

#10 andrewsw   User is offline

  • a lovely bit of linq
  • member icon

Reputation: 6891
  • View blog
  • Posts: 28,510
  • Joined: 12-December 12

Re: The database of a website/application that uses accounts

Posted 30 March 2014 - 08:26 PM

Martyr2 said:

But if you are finding yourself pulling from 5 tables at a time to make a simple query, you should really look at the structure of your data in the database rather than the database itself.

Freaky.. I was tempted to include almost exactly the same words in my previous post.
Was This Post Helpful? 0
  • +
  • -

#11 astonecipher   User is offline

  • Enterprise Software Architect
  • member icon

Reputation: 3131
  • View blog
  • Posts: 11,931
  • Joined: 03-December 12

Re: The database of a website/application that uses accounts

Posted 31 March 2014 - 03:06 PM

What queries are you using? I may be wrong but it sounds like you may be doing multiple queries when fewer would not only suffice, but would be normal to use.
Was This Post Helpful? 0
  • +
  • -

#12 dsnowdon   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 221
  • Joined: 18-January 12

Re: The database of a website/application that uses accounts

Posted 03 April 2014 - 10:58 AM

View Postastonecipher, on 31 March 2014 - 10:06 PM, said:

What queries are you using? I may be wrong but it sounds like you may be doing multiple queries when fewer would not only suffice, but would be normal to use.



I haven't started the development of this project but i know to use a join to pull from multiple tables
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1