How can I store in a db several phone numbers to be related to their own restaurants?

Question

I have a table called "restaurants" which contains each restaurant information, I want to add its phone numbers. Should I make another table called say "phones" with fields "phone1", "phone2", "phone3", etc, then make a relation between them? or their is an easier way to do this? Thanks a lot :)

Answer 1

You could make a table called "PHONE_NUMBERS" that has 2 column: "RESTAURANT_ID" and "NUMBER". Yes, the RESTAURANT_ID would have a relationship to the RESTAURANTS table.

You do this since you don't know how many phone numbers a restaurant could have. It could have 1 number, it could have 200 numbers. This design allows you to be flexible for how many phone numbers are attached to a restaurant.

Answer 2

If you know how many phone numbers each restaurant will have (i.e. how many additional columns you need), you could just add the # of columns to your current restaurants table. That'd be the simplest way, but possibly a waste of space.

The other simple option would be to create a PhoneNumbers table that will hold a foreign key to each restaurant and then a phone number. So, you can add new rows for each phone number to a restaurant when needed.