|
Having CreatedDate on everything can't hurt (as long as the column has a default value of "now"). Having a ModifiedDate on stuff where you don't need it is probably a bad idea because if you don't need it, you aren't going to be careful to update that column every time you update the row, and then it's going to be misleading.
|
# ? Jul 16, 2014 18:36 |
|
|
# ? May 30, 2024 10:43 |
|
Essential posted:How important is it to have a CreatedDate & ModifiedDate columns on tables? I've got a guy here telling me that EVERY table, without exception should have those 2 columns. We're adding a couple tables and right now I don't care about CreatedDate/ModifiedDate on a few of the tables and I can't ever see it being needed on those tables. His argument is that what IF at some point in the future I want it and even if I don't, it isn't going to affect anything negatively. That said, if you do implement this it should be automated via triggers. Or use the auditing provided by your DBMS if there is one.
|
# ? Jul 16, 2014 18:50 |
Essential posted:How important is it to have a CreatedDate & ModifiedDate columns on tables? I've got a guy here telling me that EVERY table, without exception should have those 2 columns. We're adding a couple tables and right now I don't care about CreatedDate/ModifiedDate on a few of the tables and I can't ever see it being needed on those tables. His argument is that what IF at some point in the future I want it and even if I don't, it isn't going to affect anything negatively. Personally, I like having audit fields on all of my tables (created date, created by, modified date, modified by), even if I don't foresee using that information (and there's been a few times where I'm glad I had it).
|
|
# ? Jul 16, 2014 19:05 |
|
I'd say that's probably true for data warehouses dimensions and for rows that are actually records of objects, but probably not true for things like data warehouse fact tables and application operational databases. That said, sometimes design consistency within a database is important in itself; if this is someone else's database design that you're adding to and one of the design principles is that all of their tables must have a created date and a modified date, then create the tables to that spec, but don't feel compelled to follow that principle in your own designs.
|
# ? Jul 16, 2014 19:11 |
|
As always, great advice and thank you guys. I'm going to talk with him about the db consistency and I think that's what his concern may be. I really appreciate the advice from everyone!
|
# ? Jul 16, 2014 21:54 |
|
One Swell Foop posted:That said, sometimes design consistency within a database is important in itself; ...
|
# ? Jul 16, 2014 23:38 |
|
Does anyone know if SQLite supports something similar to this: http://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert I'm going to be inserting data into a table which has an autoincrementing row id. I then would like to get back that row id to use as a key for some extra data. I have a ton of threads hitting SQL, so getting the biggest rowid is not going to cut it. Thanks! (First time using a database, please be gentle to this idiot!)
|
# ? Jul 17, 2014 05:41 |
|
No Gravitas posted:Does anyone know if SQLite supports something similar to this: http://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert
|
# ? Jul 17, 2014 06:09 |
|
I think the answer probably depends on whether you're using an API or a SQL-type interface... it looks like there's a function lastrowid in some APIs that you can use. AFAIK SQLite is unusual in that it's more often accessed using an API than it is using SQL command + driver, unlike most RDBMSs. I'm no SQLite expert so here's a stackoverflow link that describes how to use lastrowid with Python, maybe it'll help get you to where you need to be.
|
# ? Jul 17, 2014 08:40 |
|
Hello all, this should be simple but the solution escapes me. How do I turn this: pre:group player score -------------------- 1 Adam 2 1 Bob 1 1 Carl 3 1 David 2 2 Edd 3 2 Frank 2 2 George 1 2 Howard 3 pre:group player score -------------------- 1 Carl 3 2 Edd 3 I am using PostgreSQL if that matters.
|
# ? Jul 18, 2014 18:09 |
|
http://sqlfiddle.com/#!15/c6167/3SQL code:
|
# ? Jul 18, 2014 18:25 |
|
Pardot posted:http://sqlfiddle.com/#!15/c6167/3 I was so fixated on including max() somewhere in the query I forget distinct exists Thanks friend
|
# ? Jul 18, 2014 18:34 |
|
Shinku ABOOKEN posted:I was so fixated on including max() somewhere in the query I forget distinct exists Ah, but this is DISTINCT ON, not just DISTINCT, which is uhh distinct from distinct.
|
# ? Jul 18, 2014 18:37 |
|
Pardot posted:Ah, but this is DISTINCT ON, not just DISTINCT, which is uhh distinct from distinct. Noted. Thanks.
|
# ? Jul 18, 2014 18:52 |
|
Pardot posted:Ah, but this is DISTINCT ON, not just DISTINCT, which is uhh distinct from distinct. What is this black magic called and does T-SQL have an equivalent? Or are we stuck with window functions there?
|
# ? Jul 18, 2014 21:24 |
|
mobby_6kl posted:What is this black magic called and does T-SQL have an equivalent? Or are we stuck with window functions there? Nope, looks like hacky workarounds like ROW_NUMBER: http://stackoverflow.com/questions/5021693/distinct-for-only-one-column
|
# ? Jul 18, 2014 21:26 |
|
Pardot posted:Ah, but this is DISTINCT ON, not just DISTINCT, which is uhh distinct from distinct. I was also trying to find a way to use that MAX function in there, I had never heard of this DISTINCT ON. You have taught me something new, thank you sir.
|
# ? Jul 18, 2014 21:35 |
|
mobby_6kl posted:What is this black magic called and does T-SQL have an equivalent? Or are we stuck with window functions there? http://sqlfiddle.com/#!6/c6167/6 As is, that will give you all players that had the max score in their group, but you could put a GROUP BY on there to get just one. http://sqlfiddle.com/#!6/870a7/11
|
# ? Jul 18, 2014 22:13 |
|
What format do I specify a dateTime Primitive Data Type in Salesforce? I'm trying to restrict a query to objects that were created after a certain date. I just don't know what's supposed to go in here. Can I even use a greater/less than symbol in a query? code:
the fucked around with this message at 21:58 on Jul 20, 2014 |
# ? Jul 20, 2014 21:52 |
|
the posted:What format do I specify a dateTime Primitive Data Type in Salesforce? I'm trying to restrict a query to objects that were created after a certain date. Not sure for Salesforce () but most SQL software will allow ">" and ">=" for dates. You could try a general date like '7/20/14' and see if it automatically converts it to the needed format.
|
# ? Jul 20, 2014 22:17 |
|
the posted:
http://www.salesforce.com/us/developer/docs/officetoolkit/Content/sforce_api_calls_soql_select_dateformats.htm YYYY-MM-DD or YYYY-MM-DDThh:mm:ssZ edit: I've written like a total of 2 salesforce queries, the second being seeing if that date time thing was true just now, but https://workbench.developerforce.com is probably useful for figuring things out if you're not already aware of it. Pardot fucked around with this message at 05:46 on Jul 21, 2014 |
# ? Jul 21, 2014 05:43 |
|
In Salesforce, I have an Opportunity entity that has many different custom fields associated with it. What I'm finding out is that many of the custom fields are throwing errors when I make SOQL requests to them. But some aren't. And I can't figure out what is the difference, since they're all custom fields. For example, the one I want to grab is called "Training_Contact__c." When I make a request: code:
code:
|
# ? Jul 22, 2014 19:41 |
the posted:In Salesforce, I have an Opportunity entity that has many different custom fields associated with it. What I'm finding out is that many of the custom fields are throwing errors when I make SOQL requests to them. But some aren't. And I can't figure out what is the difference, since they're all custom fields. Sounds like a field level security issue. Go to Setup->Manage Users->Users. Find the user you are executing the queries as and click on the name of the Profile. Then scroll down to the "Field-Level Security" section.
|
|
# ? Jul 22, 2014 20:06 |
|
fletcher posted:Sounds like a field level security issue. Go to Setup->Manage Users->Users. Find the user you are executing the queries as and click on the name of the Profile. Then scroll down to the "Field-Level Security" section. Yep, that ended up being it! Turns out that literally the only group not given access to that field, out of the two dozen or so groups, was mine
|
# ? Jul 22, 2014 20:10 |
the posted:Yep, that ended up being it! Turns out that literally the only group not given access to that field, out of the two dozen or so groups, was mine I've been there my friend! In general, how is your Salesforce stuff going? Making progress on whatever it is you are building?
|
|
# ? Jul 22, 2014 20:16 |
|
Yeah, the main issue I've been running into is twofold: 1. Making sure that the Python queries I make are accurate (i.e. making the same query in Salesforce to prove it) 2. Showing why using Python is better than running Salesforce reports (which #1 tends to sometimes go against).
|
# ? Jul 22, 2014 20:25 |
|
the posted:Yeah, the main issue I've been running into is twofold: You could also use https://www.heroku.com/connect which two way syncs the salesforce stuff with a proper postgres database, so you can use normal tools.
|
# ? Jul 22, 2014 20:48 |
|
fletcher, while you're here, I'm trying to query a list of Opportunities and find out whether or not they have a Primary Contact. I don't see that there's a specific field for that. It looks like I can query OpportunityContactRole to see if a contact is a Primary Contact, but the problem there is that there are some Opportunities that don't have Contacts at all, so I'd have to grab a list of all Opportunities, then a list of all OpportunityContactRoles, and then filter out what doesn't match. Searching around it looks like this is a frequenty requested feature.
|
# ? Jul 22, 2014 20:55 |
|
I'm working a little program to manage full tournaments that can take on a number of forms. Since I'm defaulting to fencing tournaments as the default/base case for design purposes, I'll be working with a preliminary round that seeds a final direct elimination bracket. I'll be using local databases to manage the roster of participants and will be storing the final results there as well. I'm at a very low-level for just SQL in general and am crap at database design, so I'm just looking for some minor input on how I should structure the tables. Since I'll have a table for the roster, is it bad design to store their place initially, after the preliminary round, and the final place in the tournament in the roster table and just call SUBTABLE (I think?) to get a table for which results are needed? I'm hesitant to do this because mixing data of different types/purposes is usually a big no-no in other things, such as the division of MCV.
|
# ? Jul 22, 2014 22:03 |
the posted:Yeah, the main issue I've been running into is twofold: We also ran into limitations of what we could do with Salesforce reports and ended up going down the route of Java + SOAP API + MySQL to accomplish what we needed to do. Salesforce reports are nice but sometimes you need to break out of them for more flexibility. the posted:fletcher, while you're here, I'm trying to query a list of Opportunities and find out whether or not they have a Primary Contact. I don't see that there's a specific field for that. It looks like I can query OpportunityContactRole to see if a contact is a Primary Contact, but the problem there is that there are some Opportunities that don't have Contacts at all, so I'd have to grab a list of all Opportunities, then a list of all OpportunityContactRoles, and then filter out what doesn't match. Sounds like you might be able to use a subquery to accomplish this? Something like: code:
|
|
# ? Jul 22, 2014 22:08 |
|
Yeah I was thinking of doing that, but the problem is that there are Opportunities that exist without contacts at all. So, I have three types here: A. Opportunity with no contact B. Opportunity with OpportunityContactRole and IsPrimary = False C. Opportunity with OpportunityContactRole and IsPrimary = True And I'm trying to find a query that gets me A + B. If there was a "Does it contain a Primary Contact?" query, that would seem to solve it. But the problem is that if I make a query as you suggested I would be eliminating A which I also need. It would be like trying to find all people without red hats, but your query is "Do you have a hat? Is it red?" Would exclude people without hats in the first place. the fucked around with this message at 01:45 on Jul 23, 2014 |
# ? Jul 23, 2014 01:42 |
I think that query handles A, B, and C though. For case A, the subquery would simply have a row count of 0.
|
|
# ? Jul 23, 2014 01:51 |
|
BirdOfPlay posted:I'm working a little program to manage full tournaments that can take on a number of forms. Since I'm defaulting to fencing tournaments as the default/base case for design purposes, I'll be working with a preliminary round that seeds a final direct elimination bracket. I'll be using local databases to manage the roster of participants and will be storing the final results there as well. You have answered your own question. Putting anything not pertinent to the roster is likely to complicate matters unnecessarily, and by that I mean "Make it difficult to achieve possibly even the most basic of things" later. In all likelihood, your roster is sufficiently complicated on its own, that it shouldn't be concerned with the details of the brackets. You already have to deal with storing multiple tournaments, and people on the roster may appear on other rosters, so you have enough joins to handle on that side. Relational databases are notably (theoretically) bad at tree data, so you probably don't want to start off trying to create a query that generates the full tree for the brackets, but as you'll be dealing with a fixed number of rounds after the roster is created, you should not have any troubles. If your bracket table stores the tournament, the round, and the pairings, you should be fine. After you get your basic structure, think about what manner of referential integrity you want from the database. Indeed, it sounds like the roster is "calculated" from the bracket (with round=0), so perhaps you shouldn't store it at all; it depends entirely on what is needed on the roster.
|
# ? Jul 23, 2014 04:28 |
|
the posted:Yeah I was thinking of doing that, but the problem is that there are Opportunities that exist without contacts at all. Left join that poo poo son
|
# ? Jul 23, 2014 09:15 |
Malcolm XML posted:Left join that poo poo son This is Salesforce, there is no JOIN keyword. The subquery version that I posted should be the equivalent of a LEFT OUTER JOIN
|
|
# ? Jul 23, 2014 18:49 |
|
fletcher posted:This is Salesforce, there is no JOIN keyword. The subquery version that I posted should be the equivalent of a LEFT OUTER JOIN Yes, it worked, thanks again for your help.
|
# ? Jul 23, 2014 18:57 |
|
fletcher posted:This is Salesforce, there is no JOIN keyword. The subquery version that I posted should be the equivalent of a LEFT OUTER JOIN If it doesn't have join it's not relational Bastardized sql like dialects heraus
|
# ? Jul 23, 2014 19:01 |
|
Yo! Got some question marks for you. 1- If I'm not using the column in a WHERE clause, is it better to parse a string column to number in the SQL query or do that in my code? 2- Is there anything wrong with ODBC? I read some post dreading using "the horror that is ODBC" and now I'm curious (I forgot where I read that, probably yospos).
|
# ? Jul 23, 2014 19:07 |
|
Shinku ABOOKEN posted:Yo! Got some question marks for you. 1. Is this a once in a while query or something that will run frequently? 2. If it came from yospos then
|
# ? Jul 23, 2014 19:21 |
|
|
# ? May 30, 2024 10:43 |
|
kloa posted:1. Is this a once in a while query or something that will run frequently? It's a common query that shows stuff to the user. It will be running frequently.
|
# ? Jul 23, 2014 19:42 |