Mine is a cropped picture of my Wii Mii.
At the time it looked like me or at least my friends on FB said so.
My hair is shorter and not red now (though thinking of going back).
Mine is a cropped picture of my Wii Mii.
At the time it looked like me or at least my friends on FB said so.
My hair is shorter and not red now (though thinking of going back).
Over email:
User: Does detail excel report show not directly related summary data?
Me: No, the only place to get that summary data is on the summary report.
User: Can you put that data on the detail excel?
In this case, I think it means the Belch got you to google That Which Must Not Be Seen.
In that case. Yes, yes I did.
Belch as in a resident of ■■■■■■■. Obvious!
Of course. Duh!
Wait...so how does one get Belched? Is that to immigrate to Belgium?
Reading all the posts is to posting.
This isn't new. SSRIs have always (which is as long as I have had knowledge of them) had a warning about suicide for patients under 18.
But... Oracle is the only one that gets it right
Meh, not my choice which DB to use.
I have used Oracle back at version 8 when I was a consultant.
ETA - Oh yeah I used back when I started in IT and doing QA on a PeopleSoft application with Oracle.
But...they're very different to a temp table, which sort of exist in Oracle, but I've never used them and honestly I don't see why I would. The CTEs that I write are part of the query I'm running. They don't need to persist or be reused in other queries.
I use temp tables for persistence because I am manipulating the table...though there are places where I just broke up the query into steps for readability...those I could convert to CTEs.
I will try and report back my findings. Probably not until tomorrow.
CTEs are awesome in Oracle. I think the biggest thing is that you can reuse the data there and there's no risk of additional I/O, whereas various other joins might cause some of that. Plus it makes writing a query much more modular. I can break things up into smaller pieces in a way that having a zillion complicated joins doesn't do.Not to mention being able to do aggregates in various ways and then combine them later. Trying to do that is a nightmare in a monolithic query.
Hmmm, maybe they work differently in Oracle than SQL Server.
The accepted answer indicates there is no performance benefit over temp tables.
I remember reading around there that sql server has fairly recently improved on that sort of thing.
I'm trying it out on something I've been optimizing and not happy with yet.
What DB do you use? I'm on Oracle, so I have a similar relationship with CTEs.
SQL Server. Production is 2008R2, other environments are 2012. I know WTF, because raisins.
It has CTEs.
I've started using them when I need a sequence but I struggle sometimes with getting it right. My brain conceptually has block and I end up try this, that, and whatever until I get the result I was looking for.
ETA: the WTF
If you can express it another way, the "other way" is almost certain to be about 40 times faster.
TIL - I thought I previously read that optimizer usually makes them equivalent performance-wise.
Why? These things not taught at college?
I majored in Biology. Didn't learn these things.
What until you get a copy of SQL Server and find out about table variables.
Way ahead of you. I use SQL Server (I started with SQL Server 6.5) and table variables.
You can't put indices on table variables after the insert. So I only use them for small lookups.
blueberry pancakesSorry only blue waffles ...
I am embarrassed I know what that means.
DO NOT GOOGLE!!!! NSFL
Yes, in it's purest form...but I was resisting even partial EAV,EAV is shoving unrelated things into the same table. Having all of the questions in a table and all of the answers in a table is not EAV. It's just how a relational DB works.
Yes, apparently I did not fully understand. Thank you for pointing it out.
People who design full-on spacecowboy EAV databases need to be taught a lesson, with a cluebat.
Every knows you never go full spacecowboy EAV!
Edit Discurse!
It looks like it will work, but I didn't have enough imagination to think it through it under sudden pressure (which is kind of funny, since I advised @Karla to use outer joins to tack on columns just yesterday...)Thanks.
Even funnier (or not)...the previous boss+2 had a directive of no temp tables (we were told to use perm tables). I didn't start using them until after he left and I had to work directly with the architect for the first time (I already didn't like him) but he walked me through optimizing a stored procedure that was taking almost a minute because it used perm tables for processing data. We got it down to under a second. I started using them everywhere.
Our reporting person was out on medical leave for 3 months.
I was less than familiar with the complexity of data and business rules at the time.
I had to fix her reports where the data was wrong. They used perm tables, dynamic SQL and were 1000s of lines long with multiple levels derived tables.
I think temp tables make it it easier to follow what is going on.
I ended up rewriting all of them because it was faster.
As I didn't write them performance as I was learning the business rules. I am now optimizing them and the whole reporting database.
Use a temp table or tables and do it in multiple steps?Good idea, I always forget about temp tables.
I am neck deep in some complicated data manipulation and optimizing on our reporting database. I dream in temp tables.
Use a temp table or tables and do it in multiple steps?
@Lorne_Kates said:
Now try to write SELECT * FROM Employees WHERE EmployeeID = 1;That's full-on spacecowboy EAV-- where you end up reimplementing the database in the database.
Do not want.
@Lorne_Kates said:
I was thinking even if this only a little EAV it has all the bad of full on EAV.That's like saying a little bit of Javascript is as bad as full on Discourse.
LOL
EAV (as I understand it) is something like thisEntity Attribute ValueEmpoyeeID int 86FirstName string TomHiredOn datetime 1/1/2016
(I can't get the formatting right, sorry)
The example above just uses basic datatypes, but you can also use more complex datatypes as well. The Attribute column may have a F/K to an attributes table. Even though the attribute says "int" or "datetime", the value column stores it as a string/varchar b/c anything can be put in there. The DB can't guarantee referential integrity at this point.
The entity: the item being described.
The attribute or parameter: a foreign key into a table of attribute definitions. At the very least, the attribute definitions table would contain the following columns: an attribute ID, attribute name, description, data type, and columns assisting input validation, e.g., maximum string length and regular expression, set of permissible values, etc.
The value of the attribute.
Yes, in it's purest form...but I was resisting even partial EAV,
I think I am going to start with admin portion that will drive what the answers table or XML/JSON will be stored.
I did have a thought on my commute in term of the admin since each "version" was meant to be universal to being with.
I am going to use Evaluation Types (in the previous system each had 1 evaluation type and many groups that wanted modified versions of it. But all groups should get the default layout until they customize there version)
So all questions from both database combine for the new question table. I am going to start saying "evaluation type" which starts with standard evaluation of version 1 and 2. Then each group can accept the standard or that group will copy and modify the default *(or one of another group that is likely to have similar questions),
So instead of 2 versions and 20 customized by group...we will have up to 40 evaluation type, a new group by default is assigned the default version. Which then can be modified. Groups to evaluation types will be one to many. and each type gets an active start and end date.
@Lorne_Kates said:
Yes. But EAV is taking that to the nth degree. To the point where you abstract the Choice and Answer to generic tables. You end up with just one table.The pattern c62 laid out is how I've always seen question/answer objects saved.
Form (or Test or Whatever) (FormID, FormName, StartDate, EndDate)Question (QuestionID, FormID (fk), QuestionOrder, QuestionText, HintText, QuestionType (drop down, radio button, freetext, etc), QuestionAnswerMin, QuestionAnswerMax)Answer (AnswerID, QuestionID, AnswerOrder, IsCorrect, IsFreeText, IsForcedLastPosition)
Response (ResponseID, UserID, FormID, Date)ResponseQuestionAnswer (ResponseAnswerID, ResponseID, QuestionID, AnswerID, FreeTextResponse)
This what I did with this small survey app. I did quickly learn DataZen to allow the users to some useful datametrics.
@Lorne_Kates said:
It isn't full-on-space-cowboy EAV. There's some fun outer joins to get a Form + Response based on FormID and UserID. But this will do everything you need. Expand on it with some extra tables for things like section breaks, Question subsets, etc.
I was thinking even if this only a little EAV it has all the bad of full on EAV.
Since it isn't full-on-space -cowboy I can mention it as experiences.
ChoicesChoiceID (int)
ResponseID (int, F/K to Responses table)
QuestionID (int, F/K to Questions table)
AnswerID (int, F/K to Answers table)
TextField (varchar, this is when users can submit free text)
Isn't that a modified EAV?
Unless I am missing something that is similar to what I did on the very small survey app.
Let me just state my understanding:
Response to Choices is 1 -> n relationship. Each Choice has either an AnswerID or TextField.
I guess since there is a variable number of rows in Choices it isn't much different in my mind that EAV.
I think SQL releases are usually late Q2. I haven't been paying attention to this time out, though- too much on the docket this year for a SQL version bump.There might be a preview floating around by not, and what are preview builds for if not résumé projects.
Exactly. I'll see what I can find.
Since I still want supporting data and common data to be relational is a WTF to just store the different data in a column that is varchar as JSON?If you're going to store unstructured data, I'd suggest XML over JSON for the time being, given that SQL Server has much more support for it with respect to indexing, xquery, etc.
Yeah that is a good point. I don't like the verbosity of XML vs JSON.
The JSON data wouldn't be what needs optimizing for search and reports.
So I could do much of the work on data access layer.
Now, if you want to wait for SQL Server 2016, it becomes a different ballgame...
Skimming the article, I can't see the expected release date.
Responses table (contained when a survey was administered, a few other fields)
I'm not understanding how you are storing the responses and then displaying them.
The rest sounds like it mostly works for what we need.
The funny thing is I also just did the db design for a very small survey (20 questions) app that is somewhat EAV but that was after discussing with manager and we did it because it was small and the app ONLY inserts a survey, no display/edit..
I've done that, also have keyed document DBs to a relational DB.
OK, I will try this...it is for learning and/or sample code for next job.
EAV becomes pretty necessary in this sort of use case if you're stuck with relational. However, I would honestly recommend some sort of document store database storing JSON. Reporting will be interesting, (though reports on EAV tables are terrible in their own right) but if I understand the use case you should be able to summarize things in big batches (or keep running summaries on the fly).
Since I still want supporting data and common data to be relational is a WTF to just store the different data in a column that is varchar as JSON?
And since sections are on different pages to lessen the amount of data a different column for each section.
Eff that. Make a table for A, make one for B, make one for C, etc. Use outer joins to "add" the tables together, as needed (i.e., if and when you actually need to pull data from all the departments). You can carefully factor the tables so that the "main" table has all of the "common" fields, and then make tables for the things that aren't shared. To keep things sane, use the same field names for anything that is shared..The architect is right.
I do see how that is sane but I still have a mental block on having 40 tables (2 versions X 20 groups) and another 20 when a newer version comes around.
And I definitely wouldn't have a user-accessible admin for that (not that we do now--but in an ideal design a user could add a new group and/or new version)
users don't ever change the questionsVersion the questions? Report states "earlier versions" for not the current version?
"Versions" are the particular combination of questions, sections with join tables so groups can display only the sections and the questions they want.
Versioning the questions would be too much (several hundred question in each version).
And actually users do not change the questions. I made a rough admin for my own use but they are mostly not savvy enough for that. I display matrices of section X group/ question X group (pass in a section id) and clicking the cell toggles the display.
Originally, I made it database driven because I was just so tired off correcting bad table based layout on 20 view pages. Another WTF.
That seems to me a maintenance and reporting nightmare.This. My thing I posted above is a veritable nightmare for consistent reporting, unless the users don't ever change the questions (which is against the point of the design).
I'm ok with some denormalization just for this reason. But with a table 400 columns wide--I have too much denormalization.
That thread was how I learned how to change the default page that opens when you click New Query in SSMS.
first, identify your entities. IE questions, answers, groups, sections.then start making relationships betweens those entities.once you got that, normalize everything and see what you get.
Other than EAV, the only other way I can think of to be fully normalized is bunch small question/answer link tables. That seems to me a maintenance and reporting nightmare.
At one point I did consider saving the more dynamic question/answers as XML (though now I would prefer JSON). Since we don't do a ton a validation maybe that would work?
The majority of questions are radiobutton Yes/No/NA or Very Unlikely, Unlikely, Neither, Likely, Very Likely. A dozen or so are dropdowns. We have look up tables for all of those. A dozen or so date fields and a dozen or so text and date fields
that's a . usually that kind of tables smell of poor normalization.
Agreed.
may work, but, you really need to have it in one DB? can't you make a new structure and a migration script to go from one format to another?
Other than the common elements,
the versions are not compatible (unless I would union all the differing columns from the 2 answer tables) . That's more WTFery than above.
what i'm failing to understand, it has a table for evaluations and another table for questions?
Each db has it's own version of a lookup for questions, sections, questions to groups, and sections to groups.
The sections are not even the same across versions.
Each DB has Evaluations and Answers table. The Answers table is the super wide one. Evaluations table is a remnant because with the version 1 there were other evaluations types that were really not compatible. Version 1 was initially created for those other types (I don't care about those). Version 2 other driving point was to separate those other evaluation types from this one. They were rightfully separated into different applications.
And there is the likelihood of a new version of the evaluation which will not be compatible with either of previous versions.
And I'm not doing this for work, new boss is outsourcing the replacement (they are looking at packages like SalesForce)
.
I just want a sample design to practice .NET and demonstrate how I would design it if I had the power to do so.
What are the alternatives to EAV ?
I am learning .NET and want to rewrite a mini-version of an application that did at work (in ColdFusion--yes ColdFusion is TRWTF) and redesign the database.
Basically it started as a static evaluation with multiple sections (which is displayed in tabs-each tab is a separate page because of the number of questions).
Then, of course, each department wants their version to look different. Different questions, and eventually different sections.
This was started in a WTFy way by wrapping if statements around each question. I prefer much DRY-er code. And worked on making it dynamic.
Honestly it was not the most elegant. Couple years later we were able to do a version 2. Version 2 was mostly a front-end re-design and the dynamic forms built in. But we kept the database structure largely similar (I was not given leeway to change it).
The table the stores the answers is extremely wide (>400 columns) with many nulls.
And Version 2 is not compatible with Version 1 because there was a huge restructuring of the sections and questions.
I had our architect (not my favorite person for a number of reasons including his my way or no way attitude though I still learned things from him) plan to redesign it. His idea was to have different table for each version of the answers. This would be less prone to breakage.
I did not like this as not very DRY. I do see the point of breakage and is probably more performant.
It isn't a huge amount of data (less than 10K rows)
Would this actually be a use-case for EAV? If not, what is an alternative?
While the evaluations are dynamic once each version was designed it was static for a while save for spelling/grammar corrections.
Ideally one database would house both versions 1 (for each group) and 2 and be able to have future versions and allow for reporting across all version (there are many common fields across all versions and groups).
Also, if you had GD the last time, you can probably safely assume you'll get it again.
Yeah. And I was pretty sure I would get it the first time (between age and family history).
But probably all you need to do is have a simple regular blood sugar test in the morning like diabetics do, before you eat.
Actually, I passed those. I once diagnosed, I was supposed to test on wake up and 2 hours after every meal. I ate somewhat lower carb and generally had very good numbers. I also didn't have a huge appetite while pregnant and had a net 0 weight gain (so that helped).
When I did have cravings for carbs I ensured I ate a decent amount of fat and protein first.
My worst numbers when I was eating hospital food. I couldn't resist blueberry filled pancakes. The cruelty.
@Lorne_Kates said:
At every clinic and OB appointment, they have my wife do a quick "pee on this strip of paper" slip to test of urine-sugar. Always comes back normal. Except for once. At the first OB appointment at 22-weeks, it came back super elevated.Did you have anything sugary for breakfast? No. ... oh wait. We stopped for a latte (decaf, of course) first. But since might have a cold, we each got out own instead of sharing. And I realize I didn't ask for half-syrup. That might do it.
So out of curiosity I looked up the coffee place's nutritional info when I got home.
When I make a latte at home, I sweeten it with MAYBE half a teaspoon of vanilla sugar. 2g of sugar.
A small latte at this place, with however many pumps of sugar-syrup they use (and again, a full coffee, not sharing as usual)--- 43g of sugar.
Hmmm, they checked my urine for protein (b/c of the hypertension checking for pre-ecclampsia).
The GD test for me was empty stomach, blood test, 100 g of sugar water and a repeated blood test every 1 hour (3 hours total).
If I ever get pregnant again...I will ask to just assume I have GD.
My daughter was officially 36 w 5 d. It is considered late -preterm. I had both gestational diabetes and pregnancy induced hypertension (one causes bigger babies, the other smaller babies).
She was born a little under 6 lbs. Only problem was a little hypoglycemic and at first pediatrician appt was 5th percentile in weight. By 6 months she was 50th percentile.
Barring any unforeseen complications you should be good.
Congrats!
He actually passed away a few years ago but it took a while to get him into the repos...too soon?
This made me LOL literally. And unfortunately, no one who is left at work would get it.
I am so sad I cannot share the LOLs.
I know...I just didn't think my lurking was very interesting.It is if your avatar is accurate.
Yeah, I have since cut my hair, changed its color, and bought new glasses.
It was small for a few seconds then it wasn't.
Discoursistanty! <-- does this count as an @accalia
And this is totally derailing this thread.That's nothing new. IIRC the thread's title was originally "Which RDBMS is the least bad?" or something like that.
I know...I just didn't think my lurking was very interesting.
On topic: I use MS SQL Server at work, I have used Express at home, and of course, MySQL.
In previous positions I have used Oracle.
I like MS SQL Server but it is overkill for anything I do on my own and since I am already familiar with MySQL it is just easier to use.
Very much a lurker. Even was around for the old forums (though never registered).
But I spend enough time here that I use memes at home. And this is totally derailing this thread.
I've been around. I can't keep up most of the time.
I've used both HeidiSQL and SQLYog (not free but ~$60US) for MySQL.