Odds are i will have to perform a large amount of userid->username searches. So i believed, rather than getting a table such as the below

userid int PK
username text
userpass_salthash int
user_someopt1 int
user_sig text

to be split up because the below

//table1
userid int PK
username text

//table2
userid int //fk
userpass_salthash int
user_someopt1 int
user_sig text

reasoning i'd do the reason being i suspect a less complex table (i'm also able to make names no more then 32bytes basically like) is faster for searches together with less data like a bonus. However i know i possibly could be wrong so, which version must i do and what reasons beside optimisation?

You want to do the very first option (single table) for normalization and gratifaction.

Performance:

  • Should you put a catalog on (UserId, Username), you will have a covering index - which means you will not ever will need to go towards the table to obtain the Username anyway.
  • Should you place your clustered index on UserId, you will get a clustered index seek - that will finish up in the row data anyway.

Normalization:

  • Your next option enables for any user to appear in table1, although not table2. Because you likely do not want a person with no password (that can't login), I'd take into account that damaged.

My suggestion will be a clustered index on UserId. If you want the clustered index elsewhere, a covering index could be nearly as good.

To be sure that for any table that narrow, just one table is nearly certainly the best choice.

Yet another factor to include - a text datatype (if you are using MS SQL Server) is awfully wide. nvarchar(200) ought to be a lot more than wide enough. Use LOB data with discretion.

Do not bother worrying about optimizing that research unless of course you're certain it must be. Please recall the rules of Optmization Club.

  1. The very first rule of Optimisation Club is, you don't Optimize.
  2. The 2nd rule of Optimisation Club is, you don't Optimize without calculating.
  3. In case your application is running faster compared to underlying transport protocol, the optimisation has ended.
  4. One factor at any given time.
  5. No marketroids, no marketroid agendas.
  6. Testing goes on as lengthy because it needs to.
  7. If this sounds like the first evening at Optimisation Club, you need to write an evaluation situation.