help needed for db design

[bibiki]

hey there,
I need an idea on how to store some information in a database.

Let us assume that I want to build an application that allows a user to record what bars he visited each day.

The number of bars is variable, and a user typically visits more than one bar every day.

Now, my first idea is that I should build a comma-separated string out of the id's of each bar, and store it on a single column on a hypothetical table named barsVisited.

Is there perhaps some better way how to do this, or should I just proceed with this idea?

thank you in advance.

[scarcella]

Hi bibiki,

That idea looks good, i personally would have done it the same way. I think it would save a lot of disk space doing it that way and also makes the table look a lot cleaner.

Thanks,
Marais

[ChrisPadgham]

I think you should stay with a traditional relational design if you are planning to implement in a relational database. see below the table to hold bar visits.

barsVisited (visitId, PersonId, BarId, DateVisited)

the difficuly with your design is that it does not allow questions like
"how may people have visited bar X?" or "how many bars did I visit on 23/2/12?" to be answered easily

[bibiki]

Hey Marais,
thank you for your idea. However, seems like we both learned something from Chris.

Thank you Chris. That is how I will be creating my database. That suits my needs better.

kind regards.