February 14, 2016 at 8:43 am
So for now, lets say that my DB has 1 table.
For each record, I need to join some texts that relate to the item, so 1 item to many texts.
Most of these texts will come from external sources, so I have no control over their original nature.
Here's what some of the sources look like:
Source A is : ShortText, Medium text, LongText, SomeOtherText
Source B is : One great big text only
Source C is : Many texts for each item in the main table.
Source D is : as yet unknown.
My options for storing these in my DB:
(1) Individual table for each source.
(2) One wide table to include all possible columns from any source
[item number] to accommodate source (C).
(3) One single EAV Table
Illustrated: http://www.netshed.co.uk/temp/structures-20160214-153422.jpg
If it were just A,B & C; then I would go for option 1.
However, more sources can (& will be) added.
To accommodate new sources, a design change will be needed, that will have to then go through all the deployment hoops in our organisation, and around 2-3 months later, users will eventually be able to use the new source.
In fact, EAV would be the only option to to allow the dynamic additon of new sources. And EAV gets a really bad rap in just about every blog, article and journal that I've read. Seemingly with good reason.
One more factor in the mix.
A key user functionality required, is to perform text searches across all available texts in 1 call.
For 2/3 of the options above, this would necessitate a FULLTEXT search on a SchemaBound view that unions all columns into a single column.
Phew ...
So, I guess my point of writing is to gain some views & thoughts.
Any input appreciated 🙂
Many Thanks
February 14, 2016 at 8:53 am
My first thought, right off the bat: is SQL Server even the right tool for this?
Your problem does not look like a good fit for a relational database. Perhaps you should investigate other options, such as Document databases, or non-relational databases.
February 14, 2016 at 9:45 am
Bonus Prize to Hugo !
That is a brilliant consideration, and one that in theory I should spend a lot more time researching.
I have to admit, I know little about how they work.
There is a lot of relational data in my Database, as well as the less relational 'text' issue I exposed in the main post.
I'd be interested to look at NoSQLs and find out how the balance would work.
Unfortunately, whilst that all might go toward a long term final solution, the immediate term is a lot simpler.
I simply do not have access to anything but MS SQL.
And to adopt a new product, in my corporate environment; well you might see something in 2 or 3 years if lucky.
But thanks for the response.
I'm going to be sure to add that to my report and flag it for awareness going forward.
February 14, 2016 at 10:32 am
Look at something like Microsoft DocumentDB. It's an online service (like Azure SQL DB, but a document database), not a product that has to be purchased, servers provisioned for and installed
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply