1

I'm developing an app that will store MLS (Multiple Listing Service) Property Listings from several different MLS's. The majority of my customers will only use one MLS, however, some will use mutliple MLS's. My question is: What is the best method to setup the table(s)? Let's say I have 50 different MLS's. Each MLS will have roughly 80k records and 100 fields. Should I put all listings into a single table and reference by an MLSID? or should I create a new table for each MLS? Each MLS will always have the same field names and datatypes.

I would think that putting each MLS into it's own table would be best for performance, but not for scalability. If I were to put all records into a single table, how much do you think that would effect the performance? I'm also thinking a single table may be better because when the customer uses multiple MLS's, I can pull from a single table rather than multiple tables. Thoughts or ideas?

James Johnson
  • 45,496
  • 8
  • 73
  • 110
Ricketts
  • 5,025
  • 4
  • 35
  • 48

1 Answers1

1

I would put all MLS's in the same table. Normalization would dictate that if there is repeatable data, some MLS data would be stored separately as well. I don't know enough about the business to speak on that.

And if clients can have more than one MLS I would create an intersection table to explicitly show the one to many relationship.

  • That is what I was thinking as well. But how will that effect the performance? Querying a table with millions of records versus 100,000? I already have the intersection table, regardless of how I set this up. – Ricketts Sep 29 '11 at 23:31
  • Query performance will be largely dictated by indexes and statistics on the tables. Be sure to create a clustered index, as well as nonclustered indexes on highly queried columns. In the grand scheme of things, I wouldn't say that's a large and unmanageable table at all. –  Sep 29 '11 at 23:34
  • I agree that a single table is the way to go. I am not an experienced DBA by any means and am unfamiliar with indexes, but I will definitely research and implement as required. Thanks for the help! – Ricketts Sep 29 '11 at 23:59
  • @Ricketts no problem at all. If you ever need help with query optimization please feel free to reach out to me and I can walk you through a bit of it. Its fun stuff! –  Sep 30 '11 at 00:02