designing a new db .. MERGE table question

[lampstax]

Hi, I'm designing a db for a new application that involves some real estate stuff.

 

Its alot of data, and I was originally going to split them into 5 identical MyISAM tables:

 

`property_condos`, `property_singlefamily`, `property_multi`, `property_land`, `property_others`

 

and a MERGE table `properties_all` to rejoin them.

 

I couldnt simply set INSERT_METHOD because its restrictive to first / last table only.  I wanted to insert property into the table that correspond to its type.    Such as condos all go into the `property_condo` table.

 

So I relied on my script to do the job of selecting the correct table and doing the insert instead of inserting into `properties_all` and letting MERGE figure it out.

 

Now my issue:

 

Each table has an auto increment field `propertyID`, so if theres a row in each of the `property_x` table, there would be a `propertyID` = 1 in each table.  Essentially I end up with 5 rows on the merge table with `propertyID` = 1.

 

First time really working with MERGE tables so I didnt expect this. 

 

Is there any way to fix this?  Perhaps by `linking` primary auto increment keys across table?  Or is this a flaw in my db design? 

[fenway]

Sorry, I don't follow.

[gevans]

Why not use a signle table - `properties` and have a `proprty_type` field. That way all your properties are in one table, and can easily be filtered.

 

You can even have a second table - `property_types` with two fields `id` and `type`. When adding a property add the id of the property type rather than its textual value.