|
Wow never thought of it that way. Sweet thanks for the help
|
#
?
Jan 13, 2021 17:40
|
|
|
|
| # ? Apr 25, 2024 23:45 |
|
|
If each user can only have one license at a time you can just add a license_id column to the users table but if a user can have many licenses then you need the intermediary table
|
|
#
?
Jan 13, 2021 17:58
|
|
|
That’s good to know too, because I have a project coming up that will need to have like a hotel list that has identifiable and searchable properties associated to them (like “Pet Friendly”, “Beach View” etc). drat SQL seems so cool, thanks for all the help goons
|
|
#
?
Jan 13, 2021 18:08
|
|
|
I'm having to learn Calcite SQL for work. Wrapping my head around tumbling and hopping windows is a pain.
|
|
#
?
Jan 13, 2021 18:14
|
|
|
i believe the official term for that third table is a junction table. it's the most efficient way to do many-to-many relationships.
|
|
#
?
Jan 13, 2021 18:25
|
|
|
kumba posted:you probably want This setup allows any user to have multiple license types at the same time. From your framing it sounds like you chose driver's license classes to represent what you want to do. If a user can have 0 or 1 (or only ever 1) kind of license, then you would want to do a LicenceID column on the Users table, FK'd to the LicenceID Column on the Licenses table. What Kumba and I are doing is called normalization. I see I was beaten to this, but for a quick reference on normalization I like this poster. Normally you shouldn't need to go further than the Third Normal Form.
|
|
#
?
Jan 13, 2021 18:58
|
|
|
Yeah I wasn’t sure at first what method I wanted to do as more than likely not, one license will only ever be assigned to a user, but what if a situation arises where they hold multiple licenses etc? So I guess its better to do normalization to be safe. I have one more question that I hope you all can help and again sorry to be a pain. I’m using Postgres and just learned about the Daterange data type. I felt like it would be a good use of setting operation dates for a hotel but I think I’ve used it wrong and/or don’t get how to query it. For example I have this set up: Table Hotel id - serial hotel)name- varchar(250) Seasondates - daterange with this data: 1 Test Hotel daterange(‘2021-05-15’,”2021-12-21”) So when I query it using something like this: select hotel_name from hotel where seasondates between daterange('2021-01-01', '2021-12-12') and daterange('2021-09-01','2021-12-12') I get the correct result, Test Hotel ˆhoweverˆ lets say I have a place on the website where the user can say “show me hotels open between July 4th and August 8th, if I change the query to: select hotel_name from hotels where seasondates in (daterange('2021-07-04', '2021-08-08')) I don’t get any results  I know im being dumb and just don’t understand it though. My last question, which may be better suited for the javascript thread, is is there a way to take the seasondate row and return two dates from it to the templating engine so I could be like Open: seasondate[0] through seasondate[1] Thanks for all the help
|
|
#
?
Jan 13, 2021 20:05
|
|
|
It sounds like you want to use the Range operators to determine whether the range the user is interested in is entirely contained by the range you have in the database. So something like code:
|
|
#
?
Jan 14, 2021 15:00
|
|
|
That’s exactly it! Thanks!!!!
|
|
#
?
Jan 14, 2021 15:26
|
|
|
Is it possible to group results by islands of a repeating sequence? Say a table, or a subquery, that spits this out somewhat ordered:code:
|
|
#
?
Jan 15, 2021 17:59
|
|
|
It sounds like a fun problem to solve, but I’m not sure I get your question. If the above is the input, what should the output be?
|
|
#
?
Jan 15, 2021 18:59
|
|
|
There has to be some way you are ordering that to get those separate islands. Timestamp? Insertion order? If you do have a timestamp there or something then the solution given here could work https://stackoverflow.com/questions/6627924/how-do-i-find-records-that-have-the-same-value-in-adjacent-records-in-sql-server Chin Strap fucked around with this message at 19:27 on Jan 15, 2021 |
|
#
?
Jan 15, 2021 19:20
|
|
|
Is there any guarantee that a select * will return rows back in a fixed order in any sql database?
|
|
#
?
Jan 15, 2021 19:21
|
|
|
Chin Strap posted:Is there any guarantee that a select * will return rows back in a fixed order in any sql database? No, without an order clause they're not supposed to. On the row sets, the thing you're after is MATCH_RECOGNIZE, which is unfortunately an Oracle only feature right now, but is supposed to be in development for Postgres too.
|
|
#
?
Jan 15, 2021 19:45
|
|
|
Hi everyone its me again going to ask another annoying question  First of all, thank you all so much for the help. I’ve loved swapping to SQL from mongo so far, so much more intuitive and powerful, which I guess is the point. Anyways I’m wondering what you goons think is the best way to set up tables/data for the following use case: I’m making a web portal where users can complete a form each year. The form must be dated. Companies can’t have more than one form per year, but there will be many years of forms per company. So for example, lets say its a baseball team and the owner of the baseball team can go in and submit a yearly amount of pitches a pitcher threw. So, the form would essentially look like: Baseball Team Name: __________________ Player Name: ____________________ # of Fastballs: ________ # of Softballs: ________ # of Curves: ___________ # of Change ups: _________ Would it be best to normalize (is that the right word?) and make a separate table for each pitch type or would I be alright having them be just rows in the forms dedicated table? Am I better off making a table per form type (like let’s say I have one I want done for dedicated hitters, which is the same thing except # of Walks, # of Base Hits, # of HRs)? Or do a new table for each year of the form? or is there a better way to do something like this? Sorry if this is advanced, I decided to jump in the deep end :v Thanks!
|
|
#
?
Jan 15, 2021 20:03
|
|
|
How many different forms are there? Are they all roughly the same data shape? Like team, player, various integer fields? How often do you think you'll need to add new forms or change existing ones? What do you need to do with the data after it's created?
|
|
#
?
Jan 15, 2021 21:07
|
|
|
Theres about 30 forms, all of different data, with very few that share related data. It’s kind of a mess but that’s how the client wants to do it. I’m going to have the forms be emailed to a person but I’d like to be able to create some custom reports against the data. I think of most importance is I have to make a profile page that shows that team A completed forms from year 2021 that changes the forms due every year, if that makes sense. But I think I can do that with javascript and a call to the forms? So the profile page would show: Baseball Team 2021 Pitcher report 2021 Designated Hitter report Empress Brosephine fucked around with this message at 21:16 on Jan 15, 2021 |
|
#
?
Jan 15, 2021 21:13
|
|
|
Chin Strap posted:There has to be some way you are ordering that to get those separate islands. Timestamp? Insertion order? Each spool that gets manufactured gets a card with its information, all of that is in the database. If there's multiple jobs of a same product, they all might or might not get put onto the same spool, but each sub-length with its own info card. But the card gets printed when each sub-length of a production finishes. Even tho the internal key counts up sequentially, because the creation of cards at the specific machine is spaced temporally, other machines in the plant will be creating cards meanwhile and taking up numbers. So what I'm doing is filter by production machine, then order by card number, then by spool number. So if there were say three runs that went onto a single spool, three records with the same spool number would cluster. Since there's a finite amount of spools in the plant, they also may get reused. The same semi-finished product might be consumed by different machines in different departments at any time. Say if I want to create a report of how many spools went from which production machine to which consumer machine, per month, there will be multiple clusters of matching spool numbers if I sort it accordingly. An option to group these islands makes my life easy. Trying to do so by another key don't work that well, or at least I haven't spotted one. Date is quite suboptimal, since there's a day change if it happens during nightshift, or across weekends, or across a longer vacation period, when the plant is closed. Using consumer machine as a key doesn't either, since a specific spool with the same semi-finished product could go from machine A to machine B more than once in a certain time period. I'll probably figure something out, I just hoped there's some easy mode in database servers, i.e. grouping by cluster of repeating values or something like that. --edit: I'm attemping to do this poo poo entirely in SQL, so I can dump it into an Excel sheet, mail it away and forget about it. --edit: God bless Stack Overflow, maybe this poo poo helps: https://stackoverflow.com/questions/14010348/group-by-repeating-attribute Chin Strap posted:If you do have a timestamp there or something then the solution given here could work https://stackoverflow.com/questions/6627924/how-do-i-find-records-that-have-the-same-value-in-adjacent-records-in-sql-server --edit: Vegetable posted:It sounds like a fun problem to solve, but I’m not sure I get your question. If the above is the input, what should the output be? Combat Pretzel fucked around with this message at 22:13 on Jan 15, 2021 |
|
#
?
Jan 15, 2021 21:44
|
|
|
Here is what I came up with my question, although I feel like there’s a lot of redundancy and having to make a YEAR_FORM every year seems the wrong way to do it? Should I even bother referencing team_id in hitter and pitcher form or just let the year table handle it? Thanks! 
|
|
#
?
Jan 15, 2021 22:24
|
|
|
Empress Brosephine posted:Here is what I came up with my question, although I feel like there’s a lot of redundancy and having to make a YEAR_FORM every year seems the wrong way to do it? Should I even bother referencing team_id in hitter and pitcher form or just let the year table handle it? Are you trying to build a database model of a form filling system, or a database modeling the things described by the results of filling out those forms? It can be a subtle distinction, but typically the goal is to model the underlying data and the "forms" are the collection of application/frontend UIs used to populate that data. As an example, an insurance company wants to model cars, owners, accidents, not the forms used to declare ownership, insurability, or traffic reports, which could vary by state, by type of residence/citizenship, or accident type. For example, if you're modeling forms, then "One form of each type per year per company" tells you that somewhere you need a Unique (year, company,form type). This is a "composite primary key" in SQL land which permits multiple rows only if at least one of those three fields is different. Depending on what other references there are to this unique object, it may be stored in the database as (id,yr,co,type) with the regular primary key (id) and a table constraint for the uniqueness on (y,c,t). On the other hand, if the objects described by the forms are what matters, you start with "one statistic per player per company per year". This all gets very exciting when players change companies in a given year. Twice. And then return to the first company before the year is up.
|
|
#
?
Jan 16, 2021 03:27
|
|
|
That's all awesome information. I guess the forms would be a way for them to collect information, but the thing I ran into was that there isn't much shared data between the forms. Which is probably a good thing. I changed the modelling up a bit though I'm a way that works better now. I'll post a picture tomorrow. Thanks for the help.
|
|
#
?
Jan 16, 2021 05:10
|
|
|
So I think I split it up in the way of the former. I made the following tables: years submitted id year_submitted (which is every year up until like 2100) and then the rest stayed the same, except I dropped the 2020_form table and put a company_id reference into the actual form tables themselves. I'm hoping that will solve the issue/set it up for the future because now I can cross reference and cross reference
|
|
#
?
Jan 17, 2021 00:01
|
|
|
Forms are particularly challenging because of the variant data types (can’t use a single column) and the standard client desire to scale (not reasonable to do a table per form or column per question). If either of those statements aren’t true, your life is going to be way easier. I know because my last job (which I left 3 months ago) was building an internal scalable data collection tool for a large software company. Aka forms. In the world of dynamic, scalable forms, you really only have two great options. They’re both ways to build something that doesn’t require you to make schema changes for every form change. You do lose the ability to use db-level constraints on values since, to be scalable, you need to at least have shared columns for similarly typed fields (aka all integers in one column) since you’re only going to have one table to store your data. The first option is Entity-attribute-value. It’s basically a way of making each of the three parts their own tables. You’ll have one table for your forms (entities), one for your attributes (questions), and one for your responses (values). In the case of forms, 4 (at least) actually: Form, FormQuestion, FormResponse, FormQuestionResponse. You can get more complex if you want, for example by making a FormSection table that sits between Form and FormQuestion, or a table with client-side validation rules. It’s ugly and has some major trade offs but it works. It particularly shines in modeling sparse data sets. SurveyMonkey uses it to store user form data. Healthcare EMRs use it because there are an insane number of data points in healthcare and most patients only have a small percent documented. Sparse. You get the idea. The second option, and you’re going to hate to hear this, is to use a hybrid no-SQL approach. Use a relational database, but put the form structure and responses into nvarchar columns with straight JSON. This is what I did in that last app I worked on, and it worked pretty well. Compare the stored JSON for a form and its response to a literal paper in a filing cabinet - you can always look at it in the form that it existed when it was filed, which is something that is challenging to do with a RDBMS normalized structure (schema changes apply to history). If you care about aggregate reporting you can also process the JSON into a normalized format on submit, but then you also need to develop a process to synchronize the two (Do you allow editing and resubmitting? Do you ever do manual corrections to the db?). Obviously, coming from Mongo you know that document stores shine for this kind of use case. But the hybrid model is a real strong contender IMO especially with near first-class JSON support in MS SQL these days. Good luck.
|
|
#
?
Jan 17, 2021 09:23
|
|
|
So for the eav approach, the form table would just be like a form id and then the name of the form? That method seems to make sense especially if I have about 300 columns of non relational data But, reading about the nosql hybrid method makes sense, as I do want them to be able to go in and edit the forms and resubmit or let a admin go on and print them off. But like you said, and I don't know why I didn't really think of this, hit the collection > document model of mongo seems to make perfect sense to a form, just if they want to aggravate data then that might be a bit harder I guess. Dang so many options. Doesn't help that the client doesn't explain the data on the forms much  Thanks for your post
|
|
#
?
Jan 17, 2021 16:24
|
|
|
I removed it from my write-up because I didn't have time to give both methods, but since you're using PostgreSQL, note that json is supported with some indexing. It seemed like your data is more likely to fit into the simpler property model, but shrug.
|
|
#
?
Jan 17, 2021 16:48
|
|
|
Yeah I mean I guess I’m lucky in that there will never be much in-depth reporting or unions or joins needed on this data; the client just wants a way for it to auto aggregate data from all the forms of type x in year y. So keeping it simple seems like it’d be fine overall. The dashboard of showing what forms company x have and have not filled out yet I believe I can do entirely in javascript with a simple SELECT script. Granted i’m not sure when performance starts to rear its ugly head yet.
|
|
#
?
Jan 17, 2021 16:52
|
|
|
Aggregation across documents (Mongo, JSON) won't be as efficient as the EAV approach. The JSON type will support efficient queries with the index for 'find' operations, which arguably permits more efficient joins (but I'd say there are caveats to that being guaranteed). Starting down the EAV route will at least give you exposure and probably help you identify exceptions in future requests, namely when it becomes reasonable to start using json fields.
|
|
#
?
Jan 17, 2021 17:07
|
|
|
I’ll do that then, thank you both very much for the help. Its kind of crazy the “power” that relational databases have and have given computing. I can’t stop thinking of various uses that they have. Its nuts!
|
|
#
?
Jan 17, 2021 17:11
|
|
|
So I think I set up my EAV model correctly: Table Form would include: form_id (1) form_name (“Pitcher Report Card”) form_description(“This is a collection of data showing the amount of pitches thrown”) So table formquestion would include (I need to change attribute to question, data eventually entered in parenthesis): attrib_id (1) attribute_name(“number-of-fastballs”) form_id (1 - “Pitcher Report Card”) Table FormquestionsResponse: attribute_id(1 - “Number of Fastballs” ) qresponse_is(Random Serial Generated Number) value( “50”) date_submitted (CURRENT_DATE) And then table formresponse would aggregate these entries? response_id(random biserial generated) form_id(1 - “PITCHER REPORT CARD”) team_id(1 - “BOSTON FASTBALLS”) date_submitted (CURRENT_DATE) This seems like a way smarter way of doing it then breaking everything up individually. I guess my question is how would I say pull up Pitcher Report Card from tableForm and then see every response submitted? Would it be something like: code:Thanks again for all the help. I don’t mind paying one of you if you want to take the time to sit down and walk this over with me!!
|
|
#
?
Jan 17, 2021 18:42
|
|
|
What's the right way to pick a specific field from a group? This doesn't work (IBM DB2/DB400): SELECT MAX(CASE WHEN t.subID = 1 THEN t.someField ELSE NULL END) AS foo FROM someTable t GROUP BY t.ID Each group by ID will have a bunch of rows counting subID up from 1. I'd like to pick the first. If there's multiple rows, this ends up being null instead of the value of subID 1.
|
|
#
?
Jan 18, 2021 22:16
|
|
|
https://www.ibm.com/support/knowledgecenter/SSCRJT_6.0.0/com.ibm.swg.im.bigsql.commsql.doc/doc/r0000762.htmlquote:The [MAX] function is applied to the set of values derived from the argument values by the elimination of null values. Your query as written should work. Are you sure there is always one and exactly one row with subID = 1 ? As a matter of query design, it doesn't look like you want a MAX() at all, you're just using it because you only expect one valid row to exist. This form would express your intent better: code:
|
|
#
?
Jan 18, 2021 23:14
|
|
|
Let's Encrypt put up a blog post today about their new database servers, with some sides of details about how they've configured ZFS and MariaDB to work together. https://letsencrypt.org/2021/01/21/next-gen-database-servers.html
|
|
#
?
Jan 22, 2021 01:08
|
|
|
Empress Brosephine posted:So I think I set up my EAV model correctly: Haha, no need to pay anyone. It looks mostly correct, but I think you’re missing a relationship. A form has questions. A form has responses. A question has question responses. A response has question responses. A response represents someone clicking “submit”. So a response represents that Bob submitted this form on 1/1. Question responses should link to the question you’re answering (e.g. this is the response for # of fast balls) and the response that it came from (e.g this is from Bob on 1/1, not Sally on 1/3 or Bob again on 1/5). It should basically make a diamond shape. You want to be able to navigate down both paths most likely.
|
|
#
?
Jan 22, 2021 03:03
|
|
|
Yeah I ended up naturally (I guess?) figuring out I was missing a connector back…here’s the state of my DB now. Probably a bit ugly but it seems to work.  My only problem now is figuring out how to deal with quality measures table, because not every formquestionresponse will have a measure_id, so I feel like I’m wasting it on the table, but I needed a way to link back to it in the future. Thanks for the help again it really worked!
|
|
#
?
Jan 22, 2021 16:50
|
|
|
You could make a 0 to many mapping table in between, ala FormQuestionResponseQualityMeasures or something slightly less long.
|
|
#
?
Jan 24, 2021 04:25
|
|
|
I didn't think of that, that's a good idea Thanks again for all the help.
|
|
#
?
Jan 24, 2021 05:10
|
|
|
I have a question that I have been having a hell of a time figuring out a solution to. I have a view in a web app that allows a user to inspect a record from the database. In this example, lets assume the record is a `Book`. Now when they are vieiwng the `Book` they are viewing it as a book listed from a particular `Library`. A table has a bookid, as well as a libraryid. The biggest problem is on my view for the book, I need to be able to let user go forward to the next book in the library, or back to the previous book. I cannot query the entire table for all books in a particular library, that request is far too large. If I were returning a list of all the books for a library I would use offset and limit (postgres) to only return some rows and allow the user to see more as they paginate. However within the confines of the view I am creating, the only piece of information the user will have about the book is the library id, and the book id. Hitting next or previous needs to then go next or previous book in the library alphabetically. tl;dr: So my long winded explanation and question is: How do I find a particular books offset from its offset id, given a filtered list its offset is derived from. I basically need to make a query of limit 3 and an offset of the books offset - 1 in order to get the adjacent books in the alphabetical list but I need to figure out what my books offset is first. Any help greatly appreciated. If I explained this poorly please say so, I am a bit in over my head.
|
|
#
?
Jan 24, 2021 18:46
|
|
|
Skyarb posted:However within the confines of the view I am creating, the only piece of information the user will have about the book is the library id, and the book id. Hitting next or previous needs to then go next or previous book in the library alphabetically. And the same for the preceding book, less than, descending limit 1. You'd better have a good index on titles. Meanwhile you don't want to do things like "number of returned row" because it's not going to match between calls to the database (inserts, deletes, etc.). If you have books with different IDs but equivalent titles, use greater than or equal, presumably also sorted by ID so you can fetch the next ID. ps If this is a known need of the interface, you should prefetch a larger range, say +-15, so the frontend can display the next title (as a hint) and fetch directly using the ID. (If something is inserted meanwhile, "too bad"). At some point near either end, refill the buffer. PhantomOfTheCopier fucked around with this message at 00:39 on Jan 25, 2021 |
|
#
?
Jan 25, 2021 00:36
|
|
|
Skyarb posted:I have a question that I have been having a hell of a time figuring out a solution to. You can do something like this: SQL code:You can omit bookid = @id if you only need the adjacent books but not the current book. You can use the synthetic fields to tell which one is the previous book and which one is next if you're at either end of the list. Shy fucked around with this message at 12:41 on Jan 26, 2021 |
|
#
?
Jan 26, 2021 12:29
|
|
|
|
| # ? Apr 25, 2024 23:45 |
|
|
I have to figure out a query for something and I'm hoping you can help. I'm pretty sure I need to do a join but am having trouble visualizing it. I have the following table and I need a way to find the lots where the - 1 and - 2 dates do not match. So in this example I would want the query to return y-1 and or y-2, and z-1 and or z-2. X y and z are variable but each lot will be replicated two or three times with the - 0,- 1 or - 2 after them. Lot. Date X-0. 1 X-1. 2 X-2. 2 Y-0. 3 Y-1. 4 Y-2. 5 Z-0. 6 Z-1. 7 Z-2. 6
|
|
#
?
Jan 28, 2021 23:44
|
|
