Durham grad here, I loved it.
The collegiate system is 'more' than halls but less than Oxbridge, in that you don't get taught within colleges, but you maintain a (life)long relationship with your college. I think you still apply directly to college.
Each college has a different flavour. Some have a reputation of attracting 'posher' students (Castle and Hatfield in particular). Others are particularly musical, sporty, etc. although I suspect most positive Durham grads would have had a great time at whichever college they ended up, there is fierce loyalty to, and rivalry between, colleges. So I need to tell you Trevelyan is best :)
An advantage of the collegiate system is that there is - particularly in the smaller colleges - a sense of community, reinforced by mixed years in colleges and within the catered colleges (most of them) shared mealtimes. A disadvantage is that it can be claustrophobic.
When I first started, some of my (posher) friends compared it to boarding school. I'm a comp girl myself but couldn't comment.
Durham itself is full of people who didn't choose party central universities - Leeds, Manchester etc - and although all colleges will have events, being a fairly quiet person certainly wouldn't have been unusual when I was there.
Oh and it's the most beautiful place in the world.