Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ghanshyam007
Starting Member
6 Posts |
 Posted - 2011-01-19 : 01:12:40
|
| Any help will be highly appreciated .Design Question:I want to do a free text search in my project There are 4 main tables Project, ProjectMapping, ProjectUsers and ProjectArtifacts In project table project related information are saved like projectname, projectlocation,Startdata, enddate, etc.In projectmapping, table information related to the projects are saved there are 3 main columns i.e.- projectId, FieldName, FieldValue, Information saved in this tables are :-ProjectId FieldName FieldValue11111 Functional Areas 1200211111 Industries 1113211111 Partners 1148511111 Practice Areas 1300811111 Region 19001 Here field value is a foreign key to Lookup table where corresponding data are saved.In lookups data like functional area, industries, region etc. are saved(Mapping table shows which technology used for projects, which are the partners, multiple lookup value can exist for a project like a project can have multiple Industries.)In projectUsers table users Associated with the project are stored. Data :-ProjectID UserID RoleID11111 11123 1401311111 11290 1401311112 11261 1401311113 11149 1401011113 11255 14002Here UserID is reference to User table and roles are reference to lookup table.Now my need is to search all the functional area used in particular project or a functional area used by different project etc. so I thought to implement a free text search on a view which will fetch all the data from these three table and saved it as comma separated values like ProjectId ProjectCode Lessons UserIds Users RoleIds Roles FunctionalAreas IndustryIds Industries PracticeAreaIds PracticeAreas TechnologyIds Technologies11713 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL11623 NULL NULL 1.13E+14 pdurn,rgold,ejacobs 1.4E+14 Subject Matter Expert,Project Manager,Account Manager Strategic Planning 11155 Life Sciences Tools & Services 13006 Strategy NULL NULL11205 NULL NULL 11201 sbabin 14005 Project Manager Finance 11177 Pharmaceuticals 13007 Business Intelligence 16003 Hyperion Essbase11494 TEST123 Lessons Learned123 1.13E+14 cfrangos,placasse,tmanzione 1.4E+14 Subject Matter Expert,Business Analyst,Project Manager Enterprise-wide 11154 Biotechnology 13006 Strategy NULL NULL11714 Project Code Lessons 1.11E+09 AppSupport,sbabin 1.4E+09 Business Analyst,Account Manager Corporate Finance 11136 Energy Equipment & Services 13007 Business Intelligence 16036 4GLAfter creating the view I came to know that schema binding is not possible in a view which is having sub query or consist of a CTE. LThen I changed my plan to create another table with all mapping fields, which will save data as comma separated and of type varchar. And a trigger will be there in those tables (Project, ProjecMapping, ProjectUsers).When ever a record will be inserted or updated on these tables corresponding record will be updated on Projectsdata table. And on Projects data table I will create a free text catalog and able to do free text search.IS it a good idea to do?My concerns are :-1) Maintainability: Creating trigger on all three tables from where data will be updated to Projects data table.It will be hard to generated comma separated list data each time on update and insert into Projectsdata table and if a single value is deleted from mapping then select the particular data from comma separated list and delete it.2) Can’t I make a free text in some other mechanism 3) If I am able to make a comma separated list with out a sub query my problem will solve. (instead of doing all these circus.) |
|
|
|
|
|
|
|
Design Issue;