You can either use a specific DNS
(CNAME or A/AAAA) entry for each
known subdomain, or a wild-card DNS
entry that'll accept *.example.com:

$ORIGIN example.com
foo IN A 12.34.6.78
bar IN A 12.34.6.78

or

$ORIGIN example.com
* IN A 12.34.6.78

The advantage of this latter is that
no changes are required to either DNS
or Apache configuration once the
service is running. The disadvantage
is that all such wildcard lookups must
(by definition) end up returning the
same IP address.

The Apache configuration will depend
on your requirements, both for
end-user control and security. Note
that if the users have permission to
run CGI scripts on the server then
additional setup will be needed to
ensure that that's done securely.

Depending on whether content is static
or dynamic this will also affect your
configuration:

Use mod_vhost_alias to map individual virtual hosts into their
individual directories on the server.

If you really want, create a separate <VirtualHost> section for
each known site, but then you'll have
to restart Apache each time a new user
signs up

Use a single <VirtualHost> and then look at the hostname part of the
requested URL (from the $SERVER_NAME
environment variable) in the scripts
that render the output to figure out
which user's content to display.

Another solution I've used in a python website is to inspect the subdomain in a middleware. Then I check that against the database of subdomains and if I find a match I set a request level setting telling the code what subdomain I'm looking at.

It's really fairly clean and can work very well. My site currently supports form than 350 dynamic subdomains.