MySQL design question

cgraz

I'm building a property app using MySQL - PK(PropID). Many of the properties/apartments will be within the same building. So I need a table to keep track of this. The admin would tag which properties are related using the backend system. What's the best method?

I don't want to double count properties. For example, if I have 1, 3, and 5 in same building, I don't want a table that looks like this:

1 3
1 5
3 1
3 5
5 1
5 3

But, if I only had this:

1 3
1 5

How would I easily determine that property 1, 3, and 5 are all linked. I'd have to compare both fields. I've already got 9 tables for this app so far; maybe I'm just overthinking this.

big_nerd

cgraz,

Maybe I'm slow (well not maybe), However I don't understand your question.

If this is a property management program, do your properties not have addresses?

Would not not be able to store the address of the property and compare in that way?

You could even have a seperate table with property_id and address, and in the other tables you can have a column for address_id,

in which case you would be able to make any comparisons, and even pull a query of ALL properties that are on the same building.

If this is way off of what you are asking, perhaps you can provide a small example of your database diagram, and explain how you would like it to output.

cgraz

That's a great suggestion, but in this case, addresses are not an option. When the admins input the properties, they wouldn't be inputting addresses.

I was just hoping to give them a list of checkboxes with all the properties in that neighborhood/city, and let them select which of those properties (if any) fall in the same building.

big_nerd

How exactly do you store what building it is in?

You can also (when putting IN the info) have another row that denotes shared_building..

cgraz

The admins are familiar with which apartments belong in the same building. I just need a method for them to input this into the database (checkboxes).

If I inserted another row, it would have to hold more than one value which wouldn't work. I need a way to do this with a separate table.

big_nerd

Hmm..

Could you not store the data in the extra row and seperate it by commas or something and explode() it into seperate values?

Otherwise, I am unsure.. If you can give me an example (even PM me) of what one would do.. even throw it together in a quick HTML page / image maybe I can give a suggestion (an example of what the admin would do)