[SOLVED] Whats the best datatype for my problem?

[zang8027]

Ok, so i have a huge database i will be building for work. Pretty much, I have 2 things that i'm not sure how to approach.

 

I have a table called hotel. It has 2 rows. One row is called HOTEL_ID. The other is HOTEL_NAME.

I have another table thats the same but is restaurants instead of hotels.

 

 

Now, I have a third table called citySmall_table and it will have a few different rows. My question is that I want 2 rows that will be called HOTEL_ID and RESTAURANT_ID inside this citySmall_table that will be foreign keys to the other tables.

 

example: So if city A has restaurant 1, 4, and 6, i would want to add 1, 4, and 6 to my citySmall_table to the row RESTAURANT_ID.  Follow me?

 

What is the best datatype to do that. I could do character because i can add multiple characters rather than just one, but then how do i use PHP to display that? Usually I pass variables and use a WHERE clause. (WHERE ID=2) to display the information I want.  I don't want to use E_NUM because there will be TONS of restaurants and hotels and that would get to be too big to manage, plus I think that only lets you put one value in the row.

 

 

What is the best way to approach this?

[leela]

 

 

Hai,

 

i have invoice form..where i am saving into databse.but i need to save at the same time i save the form..is there any option for doing so..

 

Thanku ALL,

Leela

[Mchl]

zang8027: For IDs INTEGER datatype is most commonly used (and not without reason, it's the most convenient). Now, the database design you're describing is not the best one. You should rather have two more tables. One that will relate hotels to cities, and one that will relate restaurants to cities.

 

leela: please create your own topic for your question.

[zang8027]

ok, but the thing is i may have 500 cities.. would it be too crazy to have like 500 tables? I mean, size is not really a problem, more of just.. convinence

[Mchl]

No. You don't need one table per city.

You would need something like this

 

table hotels
hotel_id, hotel_name,...
1, 'Excelsior'
2, 'Radison'

table restaurants
restaurant_id, restaurant_name,...
1, 'Pierre\'s'
2, 'Cafe Bene'

table cities
city_id, city_name,...
1, 'New York'
2, 'Boston'

table hotels_to_cities
hotel_id, city_id
1,1   //Excelsior is in NY
2,2   //Radison is in Boston

table restaurants_to_cities
restaurant_id, city_id
1,2  //Pierre's is in Boston
2,1  //Cafe Bene is in NY

 

[zang8027]

ok i get it! Thank you!  Makes a lot more sense than what I was trying to do.