Up to you, it can work either way.
The best solution is to plug the ADSL modem into a router and with your log in details in the router. The PCs then plug into the router. The advantages are that the PCs operate independently so that neither requires the other to be on. You can also share files and resources such as printers. The other advantages are that routers will often have a built in firewall. You will need a network card in each PC, and an ADSL modem which has a network port.
More expensive options:
Get a wireless router and a wireless card for the PC upstairs, then you won't have to run cable around the house. The other PC can still connect to the router by cable.
Replace the ADSL modem with a combined router/modem or wireless/router/modem, that way you only need one box. I use the Netgear
DG834G wireless/router/modem at about £95. you can get cheaper ones at about £70 and router/modems for about £40 - see
here.
The cheapest option, if you are running XP, is to connect the two PCs via network cards and cable or wireless network cards and to enable Internet Connection Sharing (ICS). The downside is that it requires the machine connected to the modem to be on if you want to connect with the other machine.