General table structure for joined tables?

[eluser]dpdp[/eluser]
Hey guys, I know this isn't really a CI specific question, but I find this forum very helpful, so I come here first.

I asked a question later related to joining some tables, and now I have another question related to the structure involved in it.
The tables were as follows:

tbl_users (user data stored here) user_id
tbl_avatars (avatar filenames are in here with some other related info) avatar_id
tbl_avatars_users (he’s using this to join the two together) user_id, avatar_id

Can anyone explain why it might be necessary to have the tbl_avatars_users?
I don't really get why user_id can't be stored in tbl_avatars without there being any difference?

Thanks!

[eluser]rogierb[/eluser]
Yes, you can store the user_id in tbl_avatars. But there is a difference.
The only reason I can think of why you would need a pairing table would be to allow for multiple avatars or for historic purposes. What avatar has someone had over the years?

[eluser]dpdp[/eluser]
Yeah, I figured it wasn't too necessary. Especially in our case as old avatars aren't archived, they're replaced.

Is this a common practice or something? It's used in a lot of different data types on this project and my first impression is that it is just bloat. But I'm completely unaware as I'm no db expert. Some kind of performance or organizational boost?

[eluser]rogierb[/eluser]
I've seen it a lot on older projects (> 10 years). In this case it is just bloat. Generally the 'rule' is: If you need a specific field more then once, create a pairing table.

Then again the question rises, why not store the avatar in the user table?

[eluser]n0xie[/eluser]
It's the difference between a many:1 relationship or a many:many relationship. Clearly the original db designer had the notion that 1 user could have several avatars, and several users could have the same avatar. (Take a look at many to many relationships).

[eluser]dpdp[/eluser]
Well, in this case the pairing table is set to primary key on user_id. So a user can't have more than one avatar. Which was why I questioned the necessity of it all anyways.

My concern is stemming from the fact I have some guidelines for setting up this new, simple blog feature that is going to consist of only one or two of the users.
The table list he sent me is as follows

tbl_blog_entry
tbl_blog_authors
tbl_blog_entry_author (pairing)
tbl_blog_comments
tbl_blog_comments_users (pairing)
tbl_blog_entries_comments (pairing)


And I don't really understand why we can't just have
tbl_blog_entry (with author_id)
tbl_blog_authors
tbl_blog_comments (with entry_id, and user_id)

The pairing doesn't seem necessary to me if A) a entry may only have one author. B) a comment can only have one user.

[eluser]dpdp[/eluser]
Upon some afterthought one of the only benefits I can think of to the pairing, is having, say, all comments removed automatically if a user deletes their account? Similarly for entries.

Is this valid and even worth it?

[eluser]n0xie[/eluser]
Obviously I don't know all the ins and outs of your system, but this seems way more complex than needed. I can't imagine a situation where a comment has many entries, or an entry has many authors (unless of course this is part of the requirement).

From the information you have supplied:
1 author has many entries (1:many)
1 entry has many comments (1:many) and belongs to 1 author
1 comment belongs to 1 user and belongs to 1 entry

So based on this, you are correct in your assessment that pairing isn't necessary (or wanted since it will just make it more complex). Maybe you should inquire why this is 'his/her/it's' preferred database scheme?

[eluser]n0xie[/eluser]
[quote author="dpdp" date="1256050989"]Upon some afterthought one of the only benefits I can think of to the pairing, is having, say, all comments removed automatically if a user deletes their account? Similarly for entries.

Is this valid and even worth it?[/quote]
Usually you don't want this kind of behaviour if it related to a comment system. It would be weird to have responses to certain comments when there are 'gaps' there...

The usual route is to just add a boolean to the user when he is deleted to make him 'inactive'.

[eluser]dpdp[/eluser]
That sounds reasonable, thanks!

Reason I haven't asked him as he's away for a bit. I have some flexibility on this I just wanted to make sure I wasn't making uninformed executive decisions and looking like an idiot

Thanks for the input guys.