Database design question

[TechBoyJK]

I'm designing a messaging application for a website.

I'm at a cross roads as to how to design the main message table, which will include id's for both the sender and receiver.

The main user table includes both a unique ID (aka '12345') and a username (aka 'johndoe123'). Attempting normalization, I'm undecided whether to base the messages on the ID, Username, or both.

With the current structure, the ID is the primary key of the user table, and the username is a unique, permananent varchar value. After the creation of a user, both the username and ID will not change.

If I use the user ID in the message table, anytime a record is called/searched/selected a subquery or join will need to be ran to fetch the related username of the user ID. So if I want to select all 'inbox' records for a user, those inbox message records will only have the unique ID's of those members that sent that user a message. If there are 1,000 messages, all from different users, the join or additional queries will need to fetch each username for each of the 1,000 user Id's of each sender. 1,000 queries. That's alot of extra load.

I'm thinking it would probably be best to include both the unique ID AND the unique username to prevent having to use more than just a single query to fetch message records. I know this isn't perfect normalization, but I think I'd rather err on the side of performance vs. a fully normalized table structure.

Seem logical?

 

[BoberFett]

Originally posted by: TechBoyJK
After the creation of a user, both the username and ID will not change.

Don't count on that.

Also, what you're talking about is very simple joins. If an extra join to get username based on a primary key is putting to much of a strain on your database, you're doing something wrong.

select sender, message
from messages
join users recipient on messages.userid = recipient.userid
join users sender on messages.senderid = sender.userid
where recipient.username= 'TechBoyJK'

There's no need for a subquery, you're right that IS inefficient. Use table aliasing to your advantage.

 

[KIAman]

What RDBMS are you using? Normalize and let the software manage the best execution path. Have you actually ran a simulation on the overhead and determined the cost?

 

[TechBoyJK]

Originally posted by: BoberFett

Originally posted by: TechBoyJK
After the creation of a user, both the username and ID will not change.

Don't count on that.

Also, what you're talking about is very simple joins. If an extra join to get username based on a primary key is putting to much of a strain on your database, you're doing something wrong.

select sender, message
from messages
join users recipient on messages.userid = recipient.userid
join users sender on messages.senderid = sender.userid
where recipient.username= 'TechBoyJK'

There's no need for a subquery, you're right that IS inefficient. Use table aliasing to your advantage.

ID is the unique id for the recordset (primarykey) and as a business rule, the username will not change. So I can count on that. I know I would just join the two tables (usertable/messagetable) on the uniqueID, pull the message from the message table and the pull the username from the usertable.. But it seems performance would be greatly better, especially on larger recordsets to just use a single table...

 

[TechBoyJK]

Originally posted by: KIAman
What RDBMS are you using? Normalize and let the software manage the best execution path. Have you actually ran a simulation on the overhead and determined the cost?

MS-SQL

When I was studing for the SQL Server database design exam, there were specific questions about when it was not a good idea to normalize when performance gains would merit the troubles of duplicate data.

 

[nakedfrog]

Originally posted by: TechBoyJK

Originally posted by: BoberFett

Originally posted by: TechBoyJK
After the creation of a user, both the username and ID will not change.

Don't count on that.

Also, what you're talking about is very simple joins. If an extra join to get username based on a primary key is putting to much of a strain on your database, you're doing something wrong.

select sender, message
from messages
join users recipient on messages.userid = recipient.userid
join users sender on messages.senderid = sender.userid
where recipient.username= 'TechBoyJK'

There's no need for a subquery, you're right that IS inefficient. Use table aliasing to your advantage.

ID is the unique id for the recordset (primarykey) and as a business rule, the username will not change. So I can count on that. I know I would just join the two tables (usertable/messagetable) on the uniqueID, pull the message from the message table and the pull the username from the usertable.. But it seems performance would be greatly better, especially on larger recordsets to just use a single table...

I doubt you'd see a significant difference in the scenario you put forth.

 

[BoberFett]

Whatever you say. You already sound like you've made up your mind, so why ask here? Denormalize, but don't come back crying later when you discover that your "business rules that never change" have changed, and you have to now write triggers that update other tables because you didn't normalize.

 

[KLin]

Originally posted by: nakedfrog

Originally posted by: TechBoyJK

Originally posted by: BoberFett

Originally posted by: TechBoyJK
After the creation of a user, both the username and ID will not change.

Don't count on that.

Also, what you're talking about is very simple joins. If an extra join to get username based on a primary key is putting to much of a strain on your database, you're doing something wrong.

select sender, message
from messages
join users recipient on messages.userid = recipient.userid
join users sender on messages.senderid = sender.userid
where recipient.username= 'TechBoyJK'

There's no need for a subquery, you're right that IS inefficient. Use table aliasing to your advantage.

ID is the unique id for the recordset (primarykey) and as a business rule, the username will not change. So I can count on that. I know I would just join the two tables (usertable/messagetable) on the uniqueID, pull the message from the message table and the pull the username from the usertable.. But it seems performance would be greatly better, especially on larger recordsets to just use a single table...

I doubt you'd see a significant difference in the scenario you put forth.

I agree with Jethro, err nakedfrog.

 

[Markbnj]

Originally posted by: BoberFett
Whatever you say. You already sound like you've made up your mind, so why ask here? Denormalize, but don't come back crying later when you discover that your "business rules that never change" have changed, and you have to now write triggers that update other tables because you didn't normalize.

You don't know whether OP has made up his mind, or if he has, whether he is wrong and you are right, given the context of his application. Invariant user id's is not such an uncommon business rule.

Anyway, I wouldn't denormalize unless testing shows that normal form is a performance problem.

 

[imported_Dhaval00]

Originally posted by: BoberFett
Whatever you say. You already sound like you've made up your mind, so why ask here? Denormalize, but don't come back crying later when you discover that your "business rules that never change" have changed, and you have to now write triggers that update other tables because you didn't normalize.

Ignoring the tone )), there is truth in this - I see "username" more as serving the business need (similar to address, name, etc. columns). In order to ensure uniqueness, simply put a constraint on the username column. You can also create a non-clustered index if your queries are based on username.

However, since you're in control, do it properly from the get-go: Use an internal key and use that as your base for building queries. Assuming you have a clustered index on the primary [internal] key, you shouldn't have to worry about performance.

What is the primary purpose of your front-end? I ask because I see a lot of "denormalization" - mostly in data warehousing and reporting; if your primary need is to generate "uber-responsive" reports, then don't normalize. Again, approach this path with caution.

 

[GodlessAstronomer]

If you're running MS SQL Server 2008 I'd go for the normalized schema without a second thought. SQL Server 2008 has had some pretty big performance tweaks and it's likely that the DBMS is smarter than you are in terms of optimizing. Modern DBMSs are getting pretty smart.

 

[JasonCoder]

Originally posted by: GodlessAstronomer
If you're running MS SQL Server 2008 I'd go for the normalized schema without a second thought. SQL Server 2008 has had some pretty big performance tweaks and it's likely that the DBMS is smarter than you are in terms of optimizing. Modern DBMSs are getting pretty smart.

This.

 

[WildHorse]

``

 

[BoberFett]

Originally posted by: Dhaval00

Originally posted by: BoberFett
Whatever you say. You already sound like you've made up your mind, so why ask here? Denormalize, but don't come back crying later when you discover that your "business rules that never change" have changed, and you have to now write triggers that update other tables because you didn't normalize.

Ignoring the tone )), there is truth in this - I see "username" more as serving the business need (similar to address, name, etc. columns). In order to ensure uniqueness, simply put a constraint on the username column. You can also create a non-clustered index if your queries are based on username.

However, since you're in control, do it properly from the get-go: Use an internal key and use that as your base for building queries. Assuming you have a clustered index on the primary [internal] key, you shouldn't have to worry about performance.

What is the primary purpose of your front-end? I ask because I see a lot of "denormalization" - mostly in data warehousing and reporting; if your primary need is to generate "uber-responsive" reports, then don't normalize. Again, approach this path with caution.

Yeah, sorry didn't mean to get snippy. But day in and day out I deal with people who ask for advice because I've got 15+ years of experience designing software, then they proceed to do things their own way instead and it almost invariably ends up biting them in the ass later. I guess I was projecting a bit onto the OP, my apologies. :beer:

 

[brandonbull]

Originally posted by: BoberFett

Originally posted by: Dhaval00

Originally posted by: BoberFett
Whatever you say. You already sound like you've made up your mind, so why ask here? Denormalize, but don't come back crying later when you discover that your "business rules that never change" have changed, and you have to now write triggers that update other tables because you didn't normalize.

Ignoring the tone )), there is truth in this - I see "username" more as serving the business need (similar to address, name, etc. columns). In order to ensure uniqueness, simply put a constraint on the username column. You can also create a non-clustered index if your queries are based on username.

However, since you're in control, do it properly from the get-go: Use an internal key and use that as your base for building queries. Assuming you have a clustered index on the primary [internal] key, you shouldn't have to worry about performance.

What is the primary purpose of your front-end? I ask because I see a lot of "denormalization" - mostly in data warehousing and reporting; if your primary need is to generate "uber-responsive" reports, then don't normalize. Again, approach this path with caution.

Yeah, sorry didn't mean to get snippy. But day in and day out I deal with people who ask for advice because I've got 15+ years of experience designing software, then they proceed to do things their own way instead and it almost invariably ends up biting them in the ass later. I guess I was projecting a bit onto the OP, my apologies. :beer:

I feel your pain. I have to report on data from a system where the data is 100% designed for supporting the UI. They love to store string literal values and it's so much fun doing string searches and coding for different variations of spellings.