0

I have an important question so I searched and found similar question, read them and couldn't find good reason. so asked in comment and no one answered me. Thus I'm sorry if this is duplicate and I'll write two duplicate find by myself, but please help if you could.

These are two similar questions I found but not helped me:

I have many (about 200) tables in a SQL Server database and I need some attribute for some of these (about 50). I know the correct way for many to many relationship is create a new table but the problem is I don't know the exact number of property any table may need. I create two table first for attribute type and second for attribute values. Then thought about create 40 tables needing for many to many relation and about 240 tables in my DB just for now and couldn't guess DB size in next year. You may think DB Design in week but that is not, just the system is huge. Finally I decided to store attribute items from tables first and second like comma separated string in additional column in each table and save from adding more than 40 new table.

I know that need cost to interpret and parse in application side and may some trouble in queries. Additionally I wrote a function that get comma separated string and return table with one column and When I want to search something in this string use the IN operator from function result, but important issue I found that is not a normalized DB with this approach. is this wrong way? Appreciate any help.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
QMaster
  • 3,743
  • 3
  • 43
  • 56
  • 2
    **YES** most definitely this is wrong - you should really embrace **database normalization** and not try to use other, sneaky ways of doing things just because you're too lazy to do it properly.... – marc_s Oct 31 '14 at 13:45
  • 2
    Absolutely wrong. Storing multiple values in a single column violates 1NF. You even eluded to the issues already...you have to parse the column every time you want to evaluate it. Forget about performance if you take this approach because it is going to be slow!!!! If anything it sounds like maybe you should look at an EAV style of storage for this type of thing. EAV gets a lot of bad reputation but they are quite useful when used correctly. – Sean Lange Oct 31 '14 at 14:41
  • @marc_s Thanks so much for editing question and answer. I changed my mind and I don't this anymore but can you please tell me more than 240 tables in database is bad or not? Thanks again. – QMaster Oct 31 '14 at 17:04
  • @SeanLange Thanks for your heeds. I read about EAV so I think need looking it again. But anyway what do you think about performance of more than 240 tables in database? – QMaster Oct 31 '14 at 17:06
  • 2
    240 tables are no problem at all - as long as you have proper indexing in place, and write good SQL queries ... – marc_s Oct 31 '14 at 17:14
  • 1
    240 isn't a lot of tables at all for even a medium sized system. – Sean Lange Oct 31 '14 at 17:20
  • I really worried about that. Thanks so much good Programmers :) You didn't answer to this question so if you want answer please describe more and I'll accept it as answer or after some search and test I'll write an answer according to your comments. – QMaster Oct 31 '14 at 18:12

0 Answers0