SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

SLOSifl: Aug 10, 2002

Triple Tech posted:

And yet neither of you mention how that type of data is set up poorly for relational databases?

Absolutely, but there are plenty of cases where changing the DB schema or the way some piece of poo poo program interacts with it is a non-starter. I just assumed he had to deal with it in some one-off type of export situation just to get it over with.

If you frequently need to pull unique names from that mess, then yes, you're better off rethinking it. One option would be to make a surrogate table and throw a trigger on your existing one. On insert, update or delete, update your normalized table. Remember that the pseudo-table in *both* insert and update triggers is called INSERTED and you'll be fine.

My CLR solution posted on the previous page will get you your list and should be basically working. 40,000 rows isn't a lot. At least with a CLR option, you're processing the data before it ever leaves the server. Doing it client side is a perfectly workable option as well, as long as you don't have to do it that often.

# ? May 1, 2009 04:55

Adbot: ADBOT LOVES YOU

# ? May 9, 2024 21:33

Bad Titty Puker: Nov 3, 2007; Soiled Meat

This may be overkill, but it's interesting - Tony Rogerson's blog post. A MSSQL Server stored procedure that wrangles CSV strings into tables with fancy-pants data validation.

# ? May 1, 2009 05:03

Squashy Nipples: Aug 18, 2007

Triple Tech posted:

If this is someone else's data, you might as well spend the time to scrub it and do it the Right Way (tm) instead of doing it in pure SQL. If you're actually the authors/generators of this data and you're asking how to access it, shame on you! <>

Yeah, gotta pile on with this one. I primarly build reporting tools, so I don't really have to do much optimization. However, when building new tables I still spend a lot of time thinking about how to set up them in the "best" way.

Even if speed is no object, having your tables set up properly will make your life MUCH easier, trust me.

-

Today I found this calculated field in a piece of SQL in an old report:

code:

AVG(YEAR(MAAPLACT.MAA_PTP_HRE_DT)*12+MONTH(MAAPLACT.MAA_PTP_HRE_DT)) AS AVGYOS

I can't for the life of me figure out why the guy did it this way. First of all, in order to get the average Years of Service, you still need to calculate the number of months in the rundate, subtract, and multiply by 12 in the code.

My version is more accurate because it includes days, and also I get the final number back with no need for additional number cunching. Its a dynamically generated pass-through query, so the code puts the run date in.

code:

AVG( ( DAYS('03/31/2009') - DAYS(MAAPLACT.MAA_PTP_HRE_DT) ) / 365.25) AS AVGYOS

Is there any reason to do it the first way?

# ? May 1, 2009 16:26

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Sometimes you don't want the math that granular. That or someone doesn't know how to do math.

# ? May 1, 2009 16:33

MattO: Oct 10, 2003

Speaking about working with someone else's data, right now I'm working on a legacy code base that was built by people who were apparently learning to program as they built it.

In the DB there is a table with these column names:

DATABASE --- refers to project location
CHAR --- refers to project nature
COLUMN --- refers to project area

and many other non-reserved words that give no indication by looking at the table what the hell they're supposed to represent.

Ugh.

# ? May 1, 2009 18:00

Mulloy: Jan 3, 2005; I am your best friend's wife's sword student's current roommate.

I'm new to sql as a whole, so I'm building a small set of tables to work on as a tutorial, however I hit a snag.

I have one table which has a column that should be a set of rows from another table. For example, Table A has a column for supported OS and Table B has a list of different OS versions. I'd like to just have Table A refer to multiple rows from Table B, but the only thing I could come up with would be something like an array in that column.

This is just the design portion of my little project, but I'm not sure what I should be doing to solve this as it seems like it'd be a pretty common scenario. The other idea I had was that I just leave that kind of column out and then use some kind of statement to produce the result when I'm actually reporting from the database, but I'm not sure how to go about designing with that in mind, either.

# ? May 5, 2009 03:50

Nurbs: Aug 31, 2001; Three fries short of a happy meal...Whacko!

Mulloy posted:

I'm new to sql as a whole, so I'm building a small set of tables to work on as a tutorial, however I hit a snag.

I have one table which has a column that should be a set of rows from another table. For example, Table A has a column for supported OS and Table B has a list of different OS versions. I'd like to just have Table A refer to multiple rows from Table B, but the only thing I could come up with would be something like an array in that column.

This is just the design portion of my little project, but I'm not sure what I should be doing to solve this as it seems like it'd be a pretty common scenario. The other idea I had was that I just leave that kind of column out and then use some kind of statement to produce the result when I'm actually reporting from the database, but I'm not sure how to go about designing with that in mind, either.

What you're attempting to model is called a 'Many to Many' relationship. What you want to do is create a 'link' table. It consists of at least 2 columns, one for the id of the row in table A, and one for the id of a row in table B. Call this new table C

Then for each pair you want to create of a row in table A with a row in table B you add a row in table C which has the id of A and the id of B.

# ? May 5, 2009 04:42

Mulloy: Jan 3, 2005; I am your best friend's wife's sword student's current roommate.

Nurbs posted:

Then for each pair you want to create of a row in table A with a row in table B you add a row in table C which has the id of A and the id of B.

I've been reading up on this and the one unanswered question I have in this regard is:

In Table C I have all the pairings, do I need to have any special/extra columns in Table A and B to reflect this or do the IDs from A and B satisfy this functionality?

I assume you'd end up just saying "show me everything in table A for ID X where ID X shows in table C"?

# ? May 5, 2009 20:19

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Mulloy posted:

I'm new at SQL as a whole...

I have one table which has a column that should be a set of rows from another table. For example, Table A has a column for supported OS and Table B has a list of different OS versions. I'd like to just have Table A refer to multiple rows from Table B, but the only thing I could come up with would be something like an array in that column.

Mulloy posted:

In Table C I have all the pairings, do I need to have any special/extra columns in Table A and B to reflect this or do the IDs from A and B satisfy this functionality?

I assume you'd end up just saying "show me everything in table A for ID X where ID X shows in table C"?

See, the thing is, you're not just new to SQL, you're new to data modeling and you don't understand relational databases, which is fine, but you really need to read a good book or two on data modeling first. Diving right in and asking for advice on the forums is a bad idea without first learning the basic concepts.

# ? May 5, 2009 23:54

Mulloy: Jan 3, 2005; I am your best friend's wife's sword student's current roommate.

camels posted:

See, the thing is, you're not just new to SQL, you're new to data modeling and you don't understand relational databases, which is fine, but you really need to read a good book or two on data modeling first. Diving right in and asking for advice on the forums is a bad idea without first learning the basic concepts.

Honestly that's true. Have any recommendations? I've read a couple "intro" sql books but they never touched this topic in detail so I feel I'm either reading the wrong books or starting out of my pay grade so to speak.

Edit: Any books, functional or theoretical, would be great. I have the time to look into it, I just have no idea where to start, which is where the questions I've asked come in. Figured starting somewhere was better than waiting for the knowledge to come to me in a dream.

Mulloy fucked around with this message at 04:15 on May 6, 2009

# ? May 6, 2009 04:13

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Mulloy posted:

Honestly that's true. Have any recommendations? I've read a couple "intro" sql books but they never touched this topic in detail so I feel I'm either reading the wrong books or starting out of my pay grade so to speak.

Edit: Any books, functional or theoretical, would be great. I have the time to look into it, I just have no idea where to start, which is where the questions I've asked come in. Figured starting somewhere was better than waiting for the knowledge to come to me in a dream.

Sure and I totally sympathize, I started out the same way.

Fabian Pascal's "SQL and Relational Basics" is excellent but might be hard to find. "Understanding Relational Databases with examples in SQL-92" is good, probably not best. One more from Pascal- his "Practical Issues in Data Management" is one of the best books I've ever read on the subject, but it's not a primer.

Joe Celko has been recommending "The Manga Guide to Databases" everywhere. I skimmed a lot of it at amazon.com and was surprised, and despite myself I would actually recommend it. The manga storyline doesn't get in the way and it does a lucid, balanced job of explaining the concepts.

Chris Date has written some excellent books and he writes well, but I probably wouldn't recommend one as a starting point. Similarly, Celko's "Data and Databases" has much valuable stuff to learn as well, but is also a bit more advanced.

# ? May 6, 2009 04:27

Mulloy: Jan 3, 2005; I am your best friend's wife's sword student's current roommate.

Thanks. My first few test databases were all 1:1 and worked great, I just got stuck once I tried to mimic similar functionality to what my company actually does. I'll have to look for the Pascal books, but I imagine finding the manga guide will be a bit easier. The B&N in Utah seem to stock those things like candy.

# ? May 6, 2009 04:37

Squashy Nipples: Aug 18, 2007

Also, you can learn for free using Access (assuming you have Office). Now, Access can teach you some bad habits, but its free, and the underlying concepts are universal.

If you dig enough, MSDN has everything you need: several test Databases, and lots of tutorials and articles that will walk you through the process of developing databases from scratch.

Here is a quick pointer to get you started: the whole point of a relational database is to NEVER repeat any data... As soon as you find yourself replicating columns and/or fields, its time to break something off into a sub-table.

# ? May 6, 2009 16:21

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Dr.Khron posted:

Also, you can learn for free using Access (assuming you have Office). Now, Access can teach you some bad habits, but its free, and the underlying concepts are universal.

If you dig enough, MSDN has everything you need: several test Databases, and lots of tutorials and articles that will walk you through the process of developing databases from scratch.

Use a free SQL DBMS to learn on, don't use Microsoft Access for god's sake. I don't even know if you use Windows. In addition to PostgreSQL and MySQL, IBM, Microsoft and Oracle all make a full-featured developer edition of their SQL RDBMSes available for free. I would avoid Oracle as something to learn on, and I think DB2 is too cumbersome to start with, so I would go with one of the open-source products, or SQL Server if you use Windows.

Although I think SQL Server is okay, I personally would not recommend using Microsoft's SQL Server sample databases to learn on. They are chock-full of proprietary SQL Server features and have some sketchy design choices.

# ? May 6, 2009 17:55

Vince McMahon: Dec 18, 2003

Might want to try messing about on one of those sql quiz sites like sqlzoo.net

It won't teach you much about designing tables but it's still useful to see how to extract data in the right way.

# ? May 6, 2009 18:12

indulgenthipster: Mar 16, 2004; Make that a pour over

I originally started this post in the PHP questions thread but I think it is more suited here.

The problem was that I was using prepared statements, and needed to run another prepared statement inside a while (mysqli_stmt_fetch($stmt)) { } to get results based on the papid the first query pulled up. There does not seem to be an answer for this, so I looked for other ways:

I might of figured out the direction I need to go to solve my issue with multiple prepared statements. I've been doing some reading and ran into Subqueries in MySQL.

Here is the first MySQL statement, that just pulls up the information:

code:

SELECT papers.papid AS papid,papers.title AS title,papers.authors AS authors, papers.status AS status_num 
FROM papers WHERE papers.topid = ?

The second query takes the papid, checks a different table, and finds all values that have the same papid

code:

SELECT keywords.keyid AS keyid,keywords.keyword AS keyword 
FROM keywords WHERE keywords.papid = ?

How would I then combine those two into one query, then somehow pull only the paper info once, but the keywords as many times as I need? Or is that even possible?

# ? May 6, 2009 21:04

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Something feels fishy about PHP... Seems ghetto. I don't know PHP but here are some options.

A) Save the stuff from your first query into a PHP structure.
2) Learn how to do a "join" and think of some way to access the data the way you want (in the result set papers will be repeated per keyword)

# ? May 6, 2009 21:52

v1nce: Sep 19, 2004; Plant your brassicas in may and cover them in mulch.

I'm sure this has to be possible, but for the life of me I can't figure a way to do it. I've got 3 tables:

code:

articles
+-----+-------+--------------+
|  id | title | date_release |
+-----+-------+--------------+
|   1 |   abc |    123456789 |
|   2 |   def |    123456789 |
|   3 |   ghi |    123456789 |
|   4 |   jkl |    123456789 |
|   5 |   mno |    123456789 |
|   6 |   pqr |    123456789 |
|   7 |   stu |    123456789 |
|   8 |   vwx |    123456789 |
|   9 |    yz |    123456789 |
+-----+-------+--------------+

articles_reviews
+-----+------------+---------+------+
|  id | article_id | type_id | body |
+-----+------------+---------+------+
|   1 |           1|       1 | xxxx |
|   2 |           2|       2 | xxxx |
|   3 |           3|       2 | xxxx |
|   4 |           5|       1 | xxxx |
|   5 |           6|       2 | xxxx |
|   6 |           8|       1 | xxxx |
+-----+------------+---------+------+

articles_reviews_types
+----+------+
| id | name |
+----+------+
|  1 |   TV |
|  2 | Book |
+----+------+

There are other tables such as articles_interviws and articles_features, which is why articles isn't just one big table.

Normally to get all the review articles as a whole, I'd do a query like this:

code:

SELECT
 *
FROM
 articles
 JOIN      articles_reviews       on articles_reviews.article_id = articles.id
 LEFT JOIN articles_reviews_types on articles_reviews_types.id   = articles_reviews.type_id

What I want to do now is to get a limited number of articles from each article type (articles_reviews_types.id / articles_reviews.type_id), so I can create, say, a list of review types with six articles underneath each type heading.
What I'm after then, as an example resultset with a limit of two articles per type, ordered by type_id then article_id, is:

code:

+-------------+-------+--------------+------+------+
|  article_id | title | date_release | body | name |
+-------------+-------+--------------+------+------+
|           1 |   abc |    123456789 | xxxx |   TV |
|           5 |   mno |    123456789 | xxxx |   TV |
|           2 |   def |    123456789 | xxxx | Book |
|           3 |   ghi |    123456789 | xxxx | Book |
+-------------+-------+--------------+------+------+

I've googled for this one, but I can't seem to get the wording right for whatever it is I'm trying to do. Any help would be much appreiciated.

v1nce fucked around with this message at 12:23 on May 7, 2009

# ? May 7, 2009 12:09

Squashy Nipples: Aug 18, 2007

camels posted:

Use a free SQL DBMS to learn on, don't use Microsoft Access for god's sake. I don't even know if you use Windows. In addition to PostgreSQL and MySQL, IBM, Microsoft and Oracle all make a full-featured developer edition of their SQL RDBMSes available for free. I would avoid Oracle as something to learn on, and I think DB2 is too cumbersome to start with, so I would go with one of the open-source products, or SQL Server if you use Windows.

Ok... I admit that assuming he has Windows is a mistake. However, every single computer I've ever encountered in any workplace I've ever worked in has had the full MS Office Suite installed on it. I like open-source stuff too, but its silly to argue with that kind of ubiquity.

And I didn't say he had to "learn Access". Even though I use Access on occasion to build summary tables for myself, I'm happy that I don't know how to use the Access interface. You can still write freehand queries using JET.

# ? May 7, 2009 12:52

Zoracle Zed: Jul 10, 2001

v1nce posted:

I've googled for this one, but I can't seem to get the wording right for whatever it is I'm trying to do. Any help would be much appreiciated.

You're looking for a "top n by group" query.

# ? May 7, 2009 15:51

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Dr.Khron posted:

Ok... I admit that assuming he has Windows is a mistake. However, every single computer I've ever encountered in any workplace I've ever worked in has had the full MS Office Suite installed on it. I like open-source stuff too, but its silly to argue with that kind of ubiquity.

And I didn't say he had to "learn Access". Even though I use Access on occasion to build summary tables for myself, I'm happy that I don't know how to use the Access interface. You can still write freehand queries using JET.

Well, I think that SQL Server Express is a better choice, especially for learning SQL RDBMS. It's free and installing it is really not that big a deal. You get a nice toolset as well. Access is build on a filesystem database and won't claim that it's a SQL RDBMS. It has some fairly gross proprietary non-relational features. I really wouldn't recommend it for learning SQL RDBMS...

Bad Titty Puker fucked around with this message at 18:55 on May 7, 2009

# ? May 7, 2009 18:46

SLOSifl: Aug 10, 2002

SQL Express is great. Other than having an integrated SQL Agent for job scheduling and 4GB database file size limits, it's an extremely good free database with great tools.

# ? May 7, 2009 19:53

Contero: Mar 28, 2004

I have pretty much no real world experience with working with databases, but I do know a bit about creating SQL queries. I've often seen jobs that are looking for SQL/Oracle experience or knowledge. What would they be looking for that's specific to Oracle? The only Oracle specific detail I ever ran into learning SQL was something having to do with string or date types, and it doesn't seem like that would be enough to mention it on a job posting.

# ? May 8, 2009 01:47

var1ety: Jul 26, 2004

Contero posted:

I have pretty much no real world experience with working with databases, but I do know a bit about creating SQL queries. I've often seen jobs that are looking for SQL/Oracle experience or knowledge. What would they be looking for that's specific to Oracle? The only Oracle specific detail I ever ran into learning SQL was something having to do with string or date types, and it doesn't seem like that would be enough to mention it on a job posting.

PL/SQL, tuning (bind peeking / histograms / cost based optimizer), understanding Oracle's multiversioning system, knowledge of built in features and packages (analytic functions, external tables, materialized views, Oracle Text, function indexes, some amount of the dbms packages - dbms_xplan, dbms_job, dbms_lock, dbms_alert). Some knowledge of the differences between 9i/10g/11g is also useful because you can single out things Oracle engineers found lacking from one version to the next and added to the system.

# ? May 8, 2009 04:43

supster: Sep 26, 2003; I'M TOO FUCKING STUPID
TO READ A SIMPLE GRAPH

I have a schema design question.

Let's say I have two tables: photos and videos, representing photo and video entities. There is a third table, comments, which represents comments associated with a photo entity or a video entity. The obvious problem is that you can't have a foreign key from comments to photos and videos, since they are two separate tables.

My solution is to have an intermediate table, threads, that bridges the gap by representing a thread of comments. The photos and videos tables will have a foreign key to the threads table, and the comments table will have a foreign key to the threads table.

Is this the proper solution? It seems odd because the threads table ends up completely void of anything other than a single primary key column.

# ? May 8, 2009 08:39

v1nce: Sep 19, 2004; Plant your brassicas in may and cover them in mulch.

Zoracle Zed posted:

You're looking for a "top n by group" query.

Thanks Zoracle, thats exactly what I was after.

In case anyone else ever needs this information, here's the path and solution I took:
A lot of articles discussed how MySQL doesn't support the "TOP N" query SQL Server does, and had a rundown of several alternatives including self-joined tables which didn't scale at all, and creating a rank column for each group by detecting a change in type.
I really didn't like the idea of using self-joins, and unfortunately I couldn't get queries requiring the running of "SET @var:=0;" first, to work. I then found an article on displaying the query row number which set the variables in a subquery.

Using that information, I managed to create the following query, which will hopefully scale a tinsy bit better and is just one query with no other requirements.

code:

SELECT
      *
    # The rownum must be determined here.
    # If you do it inside the subquery the values are applied before the ORDER BY is applied,
    # causing the IF() test to be applied on meaningless values
    # Here we're storing the previous group identifier in @c and
    # incrementing @r for each subsequent row.
    # If @c is ever different (a new group is encountered) we reset @r.
    , @r := IF(type_id = @c, @r+1, 1) as rownum
    , @c := type_id as rowtmp
FROM
(
    # This subquery contains your regular query to get the data.
    # the only special additions are one subquery in the FROM list,
    # and the group column being first in the ORDER BY list.
    SELECT
          articles.title              as title
        , articles.date_release       as date_release
        , articles_reviews.body       as body
        , articles_reviews.type_id    as type_id
        , articles_reviews_types.name as type_name
    FROM
        articles
        JOIN      articles_reviews       on articles_reviews.article_id = articles.id
        LEFT JOIN articles_reviews_types on articles_reviews_types.id   = articles_reviews.type_id
        # special subquery to set the persistent variables
        , (SELECT @c:=0, @r:=0) as reset
    WHERE
        articles.date_release < NOW()
    ORDER BY
        # Order by the column (group) you're determining between here
        # then order by your own criteria
          articles_reviews.type_id
        , articles.date_release
) as gr
# Cuts out the extra items from the groups.
# Limits each group to the value specified in HAVING.
GROUP BY
      type_id
    , rownum
HAVING
    rownum <= 2
ORDER BY
# Now reorder rows by columns you actually want the results in,
# should you not actually want the results in groupings.
    date_release

v1nce fucked around with this message at 11:26 on May 8, 2009

# ? May 8, 2009 11:23

Vince McMahon: Dec 18, 2003

v1nce posted:

Couldn't you do a UNION instead?

Like SELECT * FROM blah WHERE article = 1 ORDER BY date LIMIT 6 UNION SELECT * FROM blah WHERE article = 1 ORDER BY date LIMIT 6 etc etc

# ? May 8, 2009 12:09

indulgenthipster: Mar 16, 2004; Make that a pour over

I need help figuring out a MySQL query that might take someone just a couple of minutes. I'm willing to throw out some money for this, is this the place to ask for paid help?

# ? May 8, 2009 14:53

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Just ask and you may even get someone to help you without coughing up money! :ssh:

# ? May 8, 2009 15:34

indulgenthipster: Mar 16, 2004; Make that a pour over

Alright I'll give it a shot then!

Here's my current query:

code:

SELECT   conf_review_fields.fieldid    AS fieldid,
         conf_review_fields.fieldtype  AS fieldtype,
         conf_review_fields.parentid   AS parentid,
         conf_review_fields.fieldvalue AS fieldvalue
FROM     conf_review_fields,
         conf_review_form
WHERE    conf_review_form.topid = ?
         AND conf_review_form.fieldid IN (conf_review_fields.fieldid,conf_review_fields.parentid)
ORDER BY conf_review_form.fieldorder ASC

The relevant database structure for conf_review_fields and conf_review_form

CONF_REVIEW_FIELDS

code:

fieldid  	mediumint(8)    	   	 
fieldtype 	tinyint(3)	  	  	 
parentid 	mediumint(8)  	  	  	 
fieldvalue 	varchar(150)

CONF_REVIEW_FORM

code:

topid  	mediumint(8)  	    	   	 
fieldid 	mediumint(8)	  	  	 
fieldorder 	smallint(5)

Running the above query with ? = 1, I get the following results:

code:

fieldid 	fieldtype 	parentid 	fieldvalue
1 	1 	0 	Clarity of Objectives
2 	1 	0 	Conceptual/Theoretical Rigor
13 	3 	10 	Publish with major revisions
12 	3 	10 	Publish with minor revisions
11 	3 	10 	Publish as is
10 	3 	0 	Recommendation
15 	3 	10 	Not suitable
14 	3 	10 	Revise and resubmit

What needs to happen though, is that when the fieldtype = 3, with the parentid = 0, it then has other fields that reference that parentid (for example, all of the 10s).

Is there a way to modify the query, using Group_concat or something else, that can pull those values into something I can loop through whenever a fieldtype = 3 and parentid = 0 is found?

# ? May 8, 2009 16:03

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

VerySolidSnake posted:

What needs to happen though, is that when the fieldtype = 3, with the parentid = 0, it then has other fields that reference that parentid (for example, all of the 10s).

Is there a way to modify the query, using Group_concat or something else, that can pull those values into something I can loop through whenever a fieldtype = 3 and parentid = 0 is found?

Can you clarify what you mean? I don't really follow, at all. I smell a self-joining table, so that sounds like a lot of fun...

# ? May 8, 2009 19:18

v1nce: Sep 19, 2004; Plant your brassicas in may and cover them in mulch.

Vince McMahon posted:

Couldn't you do a UNION instead?

Like SELECT * FROM blah WHERE article = 1 ORDER BY date LIMIT 6 UNION SELECT * FROM blah WHERE article = 1 ORDER BY date LIMIT 6 etc etc

You could easily do that, but it means you're UNIONing a lot of SELECTs; one for each type_id in the articles_reviews_types table, and with each additional type in that table you'd need to add another SELECT onto the query... so the only way to do that is grab the articles_reviews_types from the database, then construct your query in your favourite scripting language and send it upstairs to be run.

Not having to grab the type list from the database, construct that query and then execute all those SELECTs is hopefully what we save. Without wanting to sound like too much of an elitest wanker, UNIONing it seems like one step away from just executing a loop that runs "SELECT * FROM tables WHERE type = $type[$i] ORDER BY type_id LIMIT 5", and we all know how that turns out.

# ? May 8, 2009 22:52

indulgenthipster: Mar 16, 2004; Make that a pour over

Triple Tech posted:

Can you clarify what you mean? I don't really follow, at all. I smell a self-joining table, so that sounds like a lot of fun...

Yeah... doing some more research and talking to some people it turns out what I'm trying to do may be impossible with a single MySQL command. I instead have to build up arrays and work with them that way.

# ? May 9, 2009 02:05

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Outline your problem at a non-SQL level. Maybe your model could be improved.

# ? May 9, 2009 02:08

Vince McMahon: Dec 18, 2003

v1nce posted:

You could easily do that, but it means you're UNIONing a lot of SELECTs; one for each type_id in the articles_reviews_types table, and with each additional type in that table you'd need to add another SELECT onto the query... so the only way to do that is grab the articles_reviews_types from the database, then construct your query in your favourite scripting language and send it upstairs to be run.

Not having to grab the type list from the database, construct that query and then execute all those SELECTs is hopefully what we save. Without wanting to sound like too much of an elitest wanker, UNIONing it seems like one step away from just executing a loop that runs "SELECT * FROM tables WHERE type = $type[$i] ORDER BY type_id LIMIT 5", and we all know how that turns out.

Fair enough

# ? May 9, 2009 16:42

supster: Sep 26, 2003; I'M TOO FUCKING STUPID
TO READ A SIMPLE GRAPH

supster posted:

I have a schema design question.

Let's say I have two tables: photos and videos, representing photo and video entities. There is a third table, comments, which represents comments associated with a photo entity or a video entity. The obvious problem is that you can't have a foreign key from comments to photos and videos, since they are two separate tables.

My solution is to have an intermediate table, threads, that bridges the gap by representing a thread of comments. The photos and videos tables will have a foreign key to the threads table, and the comments table will have a foreign key to the threads table.

Is this the proper solution? It seems odd because the threads table ends up completely void of anything other than a single primary key column.

Sorry for quoting myself, but any input on this before I accept this as the right solution?

# ? May 10, 2009 02:41

Bad Titty Puker: Nov 3, 2007; Soiled Meat

supster posted:

I have a schema design question.

Let's say I have two tables: photos and videos, representing photo and video entities. There is a third table, comments, which represents comments associated with a photo entity or a video entity. The obvious problem is that you can't have a foreign key from comments to photos and videos, since they are two separate tables.

My solution is to have an intermediate table, threads, that bridges the gap by representing a thread of comments. The photos and videos tables will have a foreign key to the threads table, and the comments table will have a foreign key to the threads table.

Is this the proper solution? It seems odd because the threads table ends up completely void of anything other than a single primary key column.

supster posted:

Sorry for quoting myself, but any input on this before I accept this as the right solution?

Is what you're modeling basically message threads on a forum, where a thread can be associated with a photo or a video? Maybe threads have more attributes - something like this? --

code:

Threads (thread_id NOT NULL, 
PRIMARY KEY(thread_id),
/* can there be multiple threads about the same photo or video? */
{subforum?}, 
{forum?}, 
{thread_status? /* Open, Closed, Locked ? */, 
photo_id, 
video_id, {...},
CHECK(photo_id IS NULL  OR  video_id IS NULL), 
/* is every thread about a photo or video? if so:
CHECK(photo_id IS NOT NULL  OR  video_id IS NOT NULL)
*/
FOREIGN KEY(photo_id) REFERENCES Photos(photo_id),
FOREIGN KEY(video_id) REFERENCES Videos(video_id)
);

Posts /* comments */ (
thread_id NOT NULL, 
post_seq NOT NULL, 
PRIMARY KEY(thread_id, post_seq),
post_date NOT NULL,
comment NOT NULL, ...,
FOREIGN KEY(thread_id) REFERENCES Threads(thread_id),
);

For modeling threaded posts you will probably need to pick a model to use - nested sets, adjacency list, ... I don't have experience modeling forums/threads/posts, but I think the nested sets model is worth a look.

Bad Titty Puker fucked around with this message at 05:08 on May 10, 2009

# ? May 10, 2009 04:21

stuph: Aug 31, 2004; donkey punching my way to the top

supster posted:

Sorry for quoting myself, but any input on this before I accept this as the right solution?

Is there some sort of benefit gained by having comments of both types in the same table? If not I'd just split them (video_comment and photo_comment or something).

# ? May 10, 2009 05:23

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Relational databases don't really do subclassing really well (as noted by the potential need for a foreign key in two pools). In your model a video comment and photo comment are potentially shallow subclasses of one super class, comment. The benefit is that common attributes (which for now seems to be the entirety of the class) are maintained in one schema. Since your maintenance needs aren't that high and you don't seem to have any need to super class, you might as well make them two different tables and treat them differently and seperately.

# ? May 10, 2009 05:26

Adbot: ADBOT LOVES YOU

# ? May 9, 2024 21:33

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Triple Tech posted:

Relational databases don't really do subclassing really well (as noted by the potential need for a foreign key in two pools). In your model a video comment and photo comment are potentially shallow subclasses of one super class, comment. The benefit is that common attributes (which for now seems to be the entirety of the class) are maintained in one schema. Since your maintenance needs aren't that high and you don't seem to have any need to super class, you might as well make them two different tables and treat them differently and seperately.

I think it really depends on what "threads" and "comments" mean. If they're analogous to forums/threads/posts you could make a case for storing comments/threads about videos and those about photos in the same tables.

What if you want to write a query to list the most popular threads in the last week? Or list a user's posting history? Or identify users who post about videos more than 80% of the time?

If "about a video" or "about a photo" is an attribute of a Thread, then putting VideoComments and PhotoComments in separate tables is attribute splitting.

# ? May 10, 2009 07:40

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »