Which accelerometer and storage chip should I use to measure Foosball rod acceleration?
Your requirement is a means of measuring shaft longitudinal rotation and acceleration rates. Telling us how you think it can be best done may hide easier or better answers from you - unless accelerometers must be used for some unknown reason.

(1).  Video camera: An already built widely available high tech solution which can be adapted with an essentially free low tech add on is to use a video camera and add markings to the rods. 

For radial acceleration and velocity you can add longitudinal lines on a section of the rod that is always visible as the rod moves sideways and long enough such that as the rod slides left or right the markings at a chosen circumference do not exhibit any change. ie only haft rotation affects an image of that point.  A properly zoomed in and focused camera could produce an image that could be viewed visually in real time or slowed for checking and which allowed like;y peak acceleration or velocity portions to be checked more closely. 

Similarly, a series of circumferential stripes would allow rod sideways motion to be visually analysed.

Is video fast enough?: A quick mental Foosball game suggests you may turn the shaf 1/2 turn in 1/4 second. Faster just scales. See below.
 1/2t in 1/4s = 2 rps.
 Many modern cameras provide 30 fps, quite a few 60 fps, and a number have vastly higher frame rates.  Video far less than VGA will be adequate.
 At 30 fps a 2rps rate will produce 360 x 2 / 30 = 24 degrees per frame or about 7 frames for a full 180 degree rotation. Rotations over 1890 degree are unlikely to be very useful. BUT it is easy to either formally encode or visually examine a frame by frame record and estimate how many degrees a shaft has rotated in 1 or more frame times. The "players" already provide some input and even very simple marker and paper encoders taped to the shaft would allow as accurate determination as is liable to be useful. 

(2) Electronic optical reading Both the above stripe marking system would be easily adapted to electronic reading with an optical sensor.

At its simplest a very workable system could be implemented with some stick on paper labels and tape or glue, plus a photo transistor and photo diode detectcor or an off the shelf tx-rx reflective pair. 

eg QRB1113/4 -  one of many possible units.
 Datasheet here  



Extremely good example that comes close to this one in some respects - in this case he is reading a scale in a gas meter. 



(2) Similar to the above but use more formal rotational or linear encoding systems. eg encoder disk on shaft outside Foosball body so it is driven rotationally and either has axial moving rod slide through it OR detector hangs off rod adn wires accomodate left-right motion. 

Linear motion sensors are available - either using optical coding as above or LVDT transformers or ... .  

Some excellent LVD illustrations here each hot linked to a web page.  DIY example in image below is from here