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 »

bewilderment: Nov 22, 2007; man what

camels posted:

I recommend that you read a good introduction to database concepts and SQL before attempting to write code. What you posted isn't a syntactical issue at all -- it shows that you don't understand the fundamental concepts of relational databases. Learn those first, then look at examples of good SQL code.

I have that basic fundamental knowledge - it's the course I did about a year ago. I know that the code I posted is bad, it's practically pseudocode and not intended to be the actual thing I'd type, I was just typing quickly.

To explain what I'm actually doing, the database is linked to a web app. The intent is to, based on input, dynamically generate a listbox from one of the columns of the table. The actual schema of the database is poor but it's what I've got to work with; basically there are three tables that contain identically designed columns, and the actual contents of each table are different. The idea is say, the input to the stored procedure was 'foo', so I pull the appropriate column from the Foo table.

# ? May 24, 2011 08:22

Adbot: ADBOT LOVES YOU

# ? Jun 11, 2024 04:25

Goat Bastard: Oct 20, 2004

bewilderment posted:

I have that basic fundamental knowledge - it's the course I did about a year ago. I know that the code I posted is bad, it's practically pseudocode and not intended to be the actual thing I'd type, I was just typing quickly.

To explain what I'm actually doing, the database is linked to a web app. The intent is to, based on input, dynamically generate a listbox from one of the columns of the table. The actual schema of the database is poor but it's what I've got to work with; basically there are three tables that contain identically designed columns, and the actual contents of each table are different. The idea is say, the input to the stored procedure was 'foo', so I pull the appropriate column from the Foo table.

You still don't need a stored procedure, especially if they all have the same columns:

code:

WITH   sane_view 
       AS (SELECT 'FOO' AS source_table
                 ,foo.a
                 ,foo.b
                 ,...
           FROM   foo
           UNION ALL
           SELECT 'BAR' AS source_table
                 ,bar.a
                 ,bar.b
                 ,...
           FROM   bar
           UNION ALL
           SELECT 'BAZ' AS source_table
                 ,baz.a
                 ,baz.b
                 ,...
           FROM   baz
          )
SELECT a
      ,b
      ,...
FROM   sane_view
WHERE  source_table = upper(:table_name)

# ? May 24, 2011 10:58

benisntfunny: Dec 2, 2004; I'm Perfect.

bewilderment posted:

I have that basic fundamental knowledge - it's the course I did about a year ago. I know that the code I posted is bad, it's practically pseudocode and not intended to be the actual thing I'd type, I was just typing quickly.

To explain what I'm actually doing, the database is linked to a web app. The intent is to, based on input, dynamically generate a listbox from one of the columns of the table. The actual schema of the database is poor but it's what I've got to work with; basically there are three tables that contain identically designed columns, and the actual contents of each table are different. The idea is say, the input to the stored procedure was 'foo', so I pull the appropriate column from the Foo table.

I wouldn't call the database every time someone selects a menu item. You should build out the structure of your drop down menu in the database and have your application take in the whole structure and cache it for a given amount of time.

You can do the structure a few ways...
a table like SuperSimpleWay

code:

SELECT Option1,
       Option2,
       Option3
FROM SuperSimpleWay

Or do two tables a MenuItems table and a MenuItemRelationship table

code:

SELECT m1.MenuItemName,
       m2.MenuItemName
FROM   MenuItemRelationship mir
INNER JOIN MenuItems m1
   ON mir.MenuItemParentID = m1.MenuItemId
INNER JOIN MenuItem m2 
   ON mir.MenuItemChildID = m2.MenuItemId

The alternative takes longer to build, both writing the code and calling, but allows you to reuse the same name in different menus. If you're cacheing it for like 12-24 hours it really won't matter that it takes an extra .3 seconds to compile the list. If one of your menu items changes you can update it in one place and ensure the changes happen to all menus that call that item. -- It all depends really on how many drop down menus you'll have on your site and how anal you want to be with your database.

edit: to the other guy's point though... and I hear your reasoning... but even your pseudo code is pretty bad. You might want to really review some of your coursework prior to coding. The question of "Can you use an IF statement?" alone is enough for me to think you need to brush up on your SQL skills.

edit more: I know I said "I wouldn't call the database every time", but what i meant to say was "DO NOT WRITE YOUR STORED PROC LIKE THIS. DO NOT CALL THE DATABASE EVERYTIME."

If you can't figure out how to do the caching method where you build a menu and store it then find an alternative outside the DB like storing your menu in an XML config file somewhere on your server... hard code it into your page... do something where you're not hitting the database repeatedly like that. If your web site was large you would be opening tons of connections to perform that simple operation. It would get especially bad with people like me who will sometimes find menu items I want using arrow keys... type in the first letter of the menu item them press the down arrow till I get where I'm going. You'd hit the DB every single time I skip over an entry and run that rather lovely SQL just to find the next menu item.

benisntfunny fucked around with this message at 13:37 on May 24, 2011

# ? May 24, 2011 11:33

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

bewilderment posted:

I'm a huge newbie to SQL and SQL Server, I did a short course on basic SQL queries and database structure a year ago, and remember most of it, but some things just weren't covered.

Do stored procedures allow you to have if syntax? Specifically I'm trying to do something like this:

Create Procedure getTable
AS
@tname nvarchar(10)
IF @tname = 'foo'
(
Begin
Select * From Foo
End
)
ELSE IF @tname = 'bar'
(
Begin
Select * From Bar
End
)

Any help with the syntax of the thing would be nice. I'd test it right now but I'm not at my own computer at this moment.

Yes, you can use conditional statements. I'm not familiar with SQL Server but I understand that the syntax is

code:

IF <condition>
BEGIN
    <statements>
END

It varies slightly between SQL dialects.

edit: Didn't spot new page, not deleting this though.

# ? May 24, 2011 15:04

epswing: Nov 4, 2003; Soiled Meat

I'm starting to write tests for this database-backed application, and now realize the need for a fresh database every time the test suite is run.

I'm using SQL Server 2008 Express R2.

What I want to do is "import the schema of database X into a new database Y".

How would I automate this? If there's some way I can do this from C# that would be amazing.

# ? May 24, 2011 17:32

benisntfunny: Dec 2, 2004; I'm Perfect.

epswing posted:

I'm starting to write tests for this database-backed application, and now realize the need for a fresh database every time the test suite is run.

I'm using SQL Server 2008 Express R2.

What I want to do is "import the schema of database X into a new database Y".

How would I automate this? If there's some way I can do this from C# that would be amazing.

You need a brand new database every time? You don't just need a clean database with default data?

Why not create two databases that are identical? One is the default state and one is your test. Upon starting a new test run dump the data in your test DB and move your default state data over. Also you could do it all with one database and a script that inserts the necessary data depending on how much you have.

Creating a new database EVERY TIME a test suite is run seems like a bit much.

# ? May 24, 2011 18:13

epswing: Nov 4, 2003; Soiled Meat

benisntfunny posted:

dump the data in your test DB

By dump I guess you mean DELETE FROM or TRUNCATE. This isn't so easy with a normalized database. The tables need to be emptied in a particular order so as to not break referential integrity. Also, when the schema changes, the test database needs to stay in sync.

If I can just create a database, copy a schema, and run my tests, I don't have to do anything.

What's wrong with this?

# ? May 24, 2011 18:59

baquerd: Jul 2, 2007; by FactsAreUseless

epswing posted:

The tables need to be emptied in a particular order so as to not break referential integrity.

Did you create a circular reference that cannot be removed?

I don't see how having to empty them in a particular order makes this more challenging.

# ? May 24, 2011 19:01

benisntfunny: Dec 2, 2004; I'm Perfect.

epswing posted:

By dump I guess you mean DELETE FROM or TRUNCATE. This isn't so easy with a normalized database. The tables need to be emptied in a particular order so as to not break referential integrity. Also, when the schema changes, the test database needs to stay in sync.

If I can just create a database, copy a schema, and run my tests, I don't have to do anything.

What's wrong with this?

I'm very familiar with normalized databases and doing exactly what I mentioned. In fact I do just that activity in many test environments daily. I use DELETE just in case the copying screws up half way and needs to rollback.

I'm not sure what you mean by "I don't have to do anything" it sounds like to me you have to create a database and copy a schema over. Any way - people will do things in whatever way they like best. You might want to look into this:
http://msdn.microsoft.com/en-us/library/aa258257(v=sql.80).aspx

edit: Additionally you can write a script that drops all of the FKs and reinserts them after everything is done -- so yeah. This is essentially what Microsoft's VS Data Edition does for schema/data compares.

benisntfunny fucked around with this message at 19:07 on May 24, 2011

# ? May 24, 2011 19:04

epswing: Nov 4, 2003; Soiled Meat

baquerd posted:

I don't see how having to empty them in a particular order makes this more challenging.

Fair enough.

benisntfunny posted:

I'm not sure what you mean by "I don't have to do anything" it sounds like to me you have to create a database and copy a schema over.

Right, I can create a database and copy a schema without having to think. Say the schema changes, a new table is added. I have to (A) add that table to the test database to keep thing in sync, (B) add that table to the list of tables I'll DELETE FROM.

Neither (A) nor (B) are particularly hard, they're just "things I need to remember" which I'd happily automate, if possible.

# ? May 24, 2011 19:10

Janitor Prime: Jan 22, 2004; PC LOAD LETTER

What da fuck does that mean; Fun Shoe

I use Liquibase and a PowerShell script to automate the schemas changes and then populates the tables with the test data in one swoop without thinking about it.

# ? May 24, 2011 20:09

stray: Jun 28, 2005; "It's a jet pack, Michael. What could possibly go wrong?"

I know I'm going to get some poo poo from people here for even mentioning this accursed product's name, but I have to work with Microsoft Access. I'm trying to turn a large Excel spreadsheet into nicely-formatted reports and Access actually does make it pretty easy. Everything works except for one thing.

Right now, the Access database is using Link Tables to get data from a file that's located on my desktop. However, if other people in the office are going to use this, I need a way for the user to select the Excel file with which they want to link tables (and thus pull in the data and generate the reports). UPDATE: I've created a form with buttons that give the user a File Dialog, but how do I tell Access to use this file as the one to whose tables I want to link?

How do I do this in Access? (Is this what Macros and Modules are for?) Also, I don't know VB, so is this simple task going to be a nightmare for me?

stray fucked around with this message at 17:38 on May 26, 2011

# ? May 26, 2011 15:17

Gilg: Oct 10, 2002

I've got a table that looks like the following:

code:

PROJECT_ID FLAG_ID
1          1
1          3
1          4
2          1
2          2
3          1
3          4

and I want to end with something like

code:

PROJECT_ID TYPE
1          Alice (when flags 1, 3, and 4 are present)
2          Bob (when flag 1 is present and 3 and 4 are not)
3          Etc.. (other rules)

This is in SQL Server 2008 R2. What SQL operators am I looking for that would help me get what I would like?

# ? May 27, 2011 20:21

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Gilg posted:

I've got a table that looks like the following:
code:
PROJECT_ID FLAG_ID
1          1
1          3
1          4
2          1
2          2
3          1
3          4
and I want to end with something like
code:
PROJECT_ID TYPE
1          Alice (when flags 1, 3, and 4 are present)
2          Bob (when flag 1 is present and 3 and 4 are not)
3          Etc.. (other rules)
This is in SQL Server 2008 R2. What SQL operators am I looking for that would help me get what I would like?

Not sure what you mean; you want to selectively have each row returned have different criteria? Are the amount of those criteria set, or is it theoretically infinite? You could do subqueries but I'm not sure what you're trying to do. Also, where are the names/TYPE coming from, another table?

# ? May 27, 2011 20:37

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Gilg posted:

I've got a table that looks like the following:
code:
PROJECT_ID FLAG_ID
1          1
1          3
1          4
2          1
2          2
3          1
3          4
and I want to end with something like
code:
PROJECT_ID TYPE
1          Alice (when flags 1, 3, and 4 are present)
2          Bob (when flag 1 is present and 3 and 4 are not)
3          Etc.. (other rules)
This is in SQL Server 2008 R2. What SQL operators am I looking for that would help me get what I would like?

Could store the rules in a table or tables. Here's a quick example:

code:

Projects
PROJECT_ID  PROJECT_NAME   
1           Alice
2           Bob
--etc.

code:

ProjectFlags
PROJECT_ID  PROJECT_FLAG
1          1
1          3
1          4
2          1
2          2
3          1
3          4

code:

ProjectFlags
PROJECT_ID  PROJECT_FLAG
1          1
1          3
1          4
2          1
2          2
3          1
3          4

code:

ProjectIncludeRules
PROJECT_ID  PROJECT_FLAG
1           1
1           3
1           4
2           1
etc

code:

ProjectExcludeRules
PROJECT_ID  PROJECT_FLAG
2           3
2           4
etc

If the max number of exclude or include flags is some known small fixed number that's not going to change then it would be easier to use denormalized rules tables.

To select only projects that do not have any of the exclude flags, you could use a WHERE NOT EXISTS subquery

code:

...
AND NOT EXISTS
  (SELECT * 
   FROM ProjectFlags 
   INNER JOIN ProjectExcludeRules 
    ON ProjectExcludeRules.ProjectID = ProjectFlags.ProjectID
    AND ProjectExcludeRules.ProjectFlag = ProjectFlags.ProjectFlag)

One possible way to include only projects that have every flag as defined in the ProjectIncludeRules table would be to match the count of the rows in the ProjectFlags table, for each project, to the count of the rules in the ProjectIncludeRules table. I'll post some sample code later unless someone beats me to it.

Bad Titty Puker fucked around with this message at 21:06 on May 27, 2011

# ? May 27, 2011 20:53

Gilg: Oct 10, 2002

Scaramouche posted:

Not sure what you mean; you want to selectively have each row returned have different criteria? Are the amount of those criteria set, or is it theoretically infinite? You could do subqueries but I'm not sure what you're trying to do. Also, where are the names/TYPE coming from, another table?

Sorry, I shouldn't have put the parentheticals on the same line. I just mean that when a project has a certain combination of flags, it is a certain type. I guess it's working backwards where a PROJECT should have a TYPE and a TYPE has FLAGS, but unfortunately, PROJECT <-> FLAG is how it's setup now. Does that make sense?

The rules are very few right now, so they are not stored in the database. Currently, the middle tier code reads the flags of a project and then figures out the type, but I want to push that down into the DB layer.

# ? May 27, 2011 21:06

Bad Titty Puker: Nov 3, 2007; Soiled Meat

quote:

and I want to end with something like
code:

PROJECT_ID TYPE
1 Alice (when flags 1, 3, and 4 are present)
2 Bob (when flag 1 is present and 3 and 4 are not)
3 Etc.. (other rules)

Gilg posted:

Sorry, I shouldn't have put the parentheticals on the same line.

Not sure what you mean here.

quote:

I just mean that when a project has a certain combination of flags, it is a certain type. I guess it's working backwards where a PROJECT should have a TYPE and a TYPE has FLAGS, but unfortunately, PROJECT <-> FLAG is how it's setup now. Does that make sense?

Post some DDL (relevant lines from the CREATE TABLE statements), and it would be easier to understand what you're referring to.

quote:

The rules are very few right now, so they are not stored in the database. Currently, the middle tier code reads the flags of a project and then figures out the type, but I want to push that down into the DB layer.

That's fine as long as there aren't very many rules, but if the number of rules increases too much the SQL SELECT statements could be hard to read and maintain. In that case it might make sense to model the rules in tables.

# ? May 27, 2011 21:25

Gilg: Oct 10, 2002

camels posted:

Not sure what you mean here.

Post some DDL (relevant lines from the CREATE TABLE statements), and it would be easier to understand what you're referring to.

That's fine as long as there aren't very many rules, but if the number of rules increases too much the SQL SELECT statements could be hard to read and maintain. In that case it might make sense to model the rules in tables.

Ok, I think I've made this seem more complicated than it really is, I'm sorry. The tables in question are a PROJECT table, that has PROJECT_ID, and other columns that are not relevant, a PROJECT_FLAG table, which has PROJECT_ID and FLAG_ID, and technically a FLAG table but is also not relevant for this example.

Currently, the middle tier code reads in the entire PROJECT and PROJECT_FLAG tables, uses some business logic to figure out what the combination of flags mean in the form of a "type" (no database table associated with it) and presents the project-type combinations. Type could be "Online", "Under Maintenance", "Permanently Shutdown", etc.. based on the flags present.

I would like the project-type combination in the form of a DB query. I thought it would be something like creating a function that takes in a list of FLAG_IDs and returns a type string, and somehow applying that to the PROJECT_FLAG query, but I'm not sure.

Thanks for the help Scaramouche and camels, I greatly appreciate your patience.

# ? May 27, 2011 22:02

uG: Apr 23, 2003; by Ralp

edit: whoops

# ? May 28, 2011 05:02

G-Dub: Dec 28, 2004; The Gonz

stray posted:

I know I'm going to get some poo poo from people here for even mentioning this accursed product's name, but I have to work with Microsoft Access. I'm trying to turn a large Excel spreadsheet into nicely-formatted reports and Access actually does make it pretty easy. Everything works except for one thing.

Right now, the Access database is using Link Tables to get data from a file that's located on my desktop. However, if other people in the office are going to use this, I need a way for the user to select the Excel file with which they want to link tables (and thus pull in the data and generate the reports). UPDATE: I've created a form with buttons that give the user a File Dialog, but how do I tell Access to use this file as the one to whose tables I want to link?

How do I do this in Access? (Is this what Macros and Modules are for?) Also, I don't know VB, so is this simple task going to be a nightmare for me?

This isn't an SQL question so it is probably better placed elsewhere. Anyway you are going to have to delete any currently linked tables (see DeleteObject) and then link to the new file, the path for which you will have from your File Open Dialog (see TransferDatabase) using VBA.

# ? May 28, 2011 11:16

shelper: Nov 10, 2005; Something's still wrong with this code

Can you guys help me figure out an elegant or easy way to accomplish the following:

code:

Table 1: items.

ite_id	art_id	ite_barcode
1	1	123
2	3	1234
3	2	421
4	4	4212	
5	2	1234
6	3	421
7	1	421
8	2	412
9	1	123
10	3	423
11	5	412

code:

Table 2: articles.

art_id	art_nameart_desc
1	bleh	asfd
2	blah	zxv
3	bloh	asdf
4	bluh	zvxc
5	blih	zxc

What I'd like are all the items for the first x(let's say 4 for now) articles, ordered by ite_barcode.

So i get my items table, and sort them by ite_barcoded.

code:

Table 3: items, sorted
ite_id	art_id	ite_barcode
1	1	123
9	1	123
8	2	412
11	5	412
3	2	421
6	3	421
7	1	421
10	3	423
2	3	1234
5	2	1234
4	4	4212

I then want to get all the item IDs (or all of the item data, if possible), from the rows from the top downwards, until i have four unique articles.

So...

code:

ite_id	art_id	ite_barcode
1	1	123
	(1 unique art_id)
9	1	123
8	2	412
	(2 unique art_id)
11	5	412
	(3 unique art_id)
3	2	421
6	3	421
	(4 unique art_id)
7	1	421
10	3	423
2	3	1234
5	2	1234
4	4	4212
	(5 unique art_id)(STOP and don't use this one)

Bad example right now since it stops right at the last one because of the sorting order. Since it already has four unique IDs and i want it to stop at five, it doesn't return ite_id 4, or any others that would follow.

I can't use * from items where art_id in (distinct art_id 0,5), because then i would also get items that weren't in table 3.

I can simply read them line by line in PHP and keep track of the art_ids there, but that's so ugly it sends shivers down my spine.

I have no doubt i could put it in a stored procedure and have it do all the hard work, but I'm not as familiar with them as I'd like, and really I'm just hoping someone will point out an easy native MYSQL function i just overlooked.

Anybody any ideas?

shelper fucked around with this message at 19:30 on May 30, 2011

# ? May 30, 2011 17:10

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

i'm having a hard time understand what in the hell is supposed to be the unique identifier, can you possible give the sql your currently using and or explain it better.

# ? May 30, 2011 17:53

shelper: Nov 10, 2005; Something's still wrong with this code

Fair point, i should have been clearer.

Primary key for item table is ite_id. Foreign key is art_id, which leads to articles table. Barcode is just data.
Primary key for articles is art_id. Art_name and art_descr is just data.

*edit*
Edited tables for (hopeful) clarity. I'm at home right now so i don't have access to my code so no SQL for now.

shelper fucked around with this message at 19:32 on May 30, 2011

# ? May 30, 2011 19:21

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

shelper posted:

Fair point, i should have been clearer.

Primary key for item table is ite_id. Foreign key is art_id, which leads to articles table. Barcode is just data.
Primary key for articles is art_id. Art_name and art_descr is just data.

*edit*
Edited tables for (hopeful) clarity. I'm at home right now so i don't have access to my code so no SQL for now.

Ah okay well you can't do a limit in a subselect but you can do it in a join table select.

So lets do that.

code:

select t1.ite_id, t1.art_id, t1.ite_barcode from items as t1

join (select  art_id from items  group by art_id order by ite_barcode  limit 4) as t2 on t1.art_id = t2.art_id
order by t1.ite_barcode

I added more data and stuff to make sure it wasn't going to flow over.

articles table

Items Table

Sql results

# ? May 30, 2011 21:28

shelper: Nov 10, 2005; Something's still wrong with this code

Right!
That's more or less what i have so far.

But say there are a bajillion more items down the table. Since they appear *after* the fifth art_id, i don't want them either.

Yaknow what, maybe there is an easier way to deal with this. I'm positive i can't be the first to have dealt with this.

CONTEXT

Think of an inventory supply database. Articles are what you possibly could have, items are what you really have. Articles store generic data like article name and description, while item stores specific data for that article, like expiration date.

Everytime an item enters your storehouse, you create an instance of the article in the item table.

Now i sort all the items in your storehouse in a little web-interface table. For easy viewing, i group all the items by art_id number so that i will always have 5 unique articles listed. (with possible multiple collapsed items).
So instead of having a table filled with rows saying

code:

Art_name      expiry_date
POTATOES      01-10-2011
POTATOES      01-06-2011
POTATOES      01-12-2011
POTATOES      01-09-2011

You just see:

Art_name expiry_date
[4]+ POTATOES 01-10-2011

Where the + thing is a little doohicky that collapses rows and all that fun stuff.

Now then, this all works well and good when you are sorting the items on a property of the article _{(Select * from items i join (select art_id from articles order by art_name, limit 0,5)a on i.art_id = a.art_id.)(or something like that)} and it will give me all the items belonging to those articles.

But what if i need to order it on a property of the items, say expiry date?

I could simply ask for items i order by ite_expiry_date but then i could have this:

code:

Art_name      expiry_date
POTATOES      01-02-2011
APPLES        01-04-2011
POTATOES      01-06-2011
APPLES        01-09-2011
POTATOES      01-11-2011
APPLES        01-12-2011

And i still want them at least semi-sorted by article, so i can group them efficiently. Besides, how would i know how many to limit? I always like to have 5 unique articles show per table-page, and i can't control how many articles i can view this way.

I can't simply ask (* from items i join (distinct art_id from items i limit 0,5 order by ite_barcode) a on a.art_id = i.art_id) because then I'd get lots of potatoes that might not yet expire until 2200, which don't belong on the first page of items that expire early.

Which then leads back to the original question:

I want the item_ids of the first expiring items, but only for the first five unique articles. I don't want the item ids of the same article after the unique counter has been hit (they would go on the second page).

Thanks for helping!

shelper fucked around with this message at 23:23 on May 30, 2011

# ? May 30, 2011 23:18

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

I would need to see how the tables and things are stored to even venture a guess but i imagine a sub select with a min() on the date date joining with something on an id to pull the right ones would work.

Or even just a group on the type and a Min() on the date might work depending.

# ? May 30, 2011 23:41

shelper: Nov 10, 2005; Something's still wrong with this code

I'll play around with those, thanks.
I won't ask you to do my work for me, just sprout some ideas, which you did.

# ? May 31, 2011 07:08

Mr. Crow: May 22, 2008; Snap City mayor for life

Not sure if this should go here or the .net thread but I'll just ask here, can anyone point me towards some good tutorials for working with Datasets and Databases in VS2010?

I have an OK enough understanding of SQL (to the point where I can interact with databases in the abstract sense and 99% of the tutorials I can find are useless basic basic stuff or not at all related to what I want) but I'm having a hard time figuring out how to go about interacting with them via VS. Like what the IDE is doing automatically and what I need to do and what I don't need to do to prevent duplication etc. etc.

# ? May 31, 2011 18:58

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Mr. Crow posted:

Not sure if this should go here or the .net thread but I'll just ask here, can anyone point me towards some good tutorials for working with Datasets and Databases in VS2010?

I have an OK enough understanding of SQL (to the point where I can interact with databases in the abstract sense and 99% of the tutorials I can find are useless basic basic stuff or not at all related to what I want) but I'm having a hard time figuring out how to go about interacting with them via VS. Like what the IDE is doing automatically and what I need to do and what I don't need to do to prevent duplication etc. etc.

The .net thread is a better place. Are you using MSSQL or mysql or what that changes what you want to do with them quite a bit.

# ? May 31, 2011 19:33

Mr. Crow: May 22, 2008; Snap City mayor for life

Sprawl posted:

The .net thread is a better place. Are you using MSSQL or mysql or what that changes what you want to do with them quite a bit.

MSSQL but I'll move it to the .net thread.

# ? May 31, 2011 22:14

a cat: Aug 8, 2003; meow.

I need to design a database where there are two main entities, for example, people and cars for a car rental website. My problem is that each of these needs to have a huge number of named attributes. For example a car needs to have a "music player type" (ie cd/mp3/tape). It also has attributes like make/models. I've always thought the way to handle these. These are no huge deal alone, just have a "makes" table, a "models" table, whatever, then have a foreign key "make_id" in the cars table.

But then there are also a lot of many-to-many relationships like a "features" attribute that can be things like "sunroof" or "spoiler" or whatever (it can have multiple). So these would need a "feature_names" table (essentially just an id and name), and then a "car_features table (just a car_id and a feature_id. There are probably around 20 of these many-to-many relationships for each main entity, cars and people.

My problem is this table got extremely large and unwieldy fast. This is probably the largest application I've built from scratch on my own and it may be normal, but having 40 or so sets of nearly identical tables just seems stupid.

I'm not an expert in this domain (there's a lot of technical stuff I'm not mentioning) and I may be missing ways to better group the data, but in general is there any solution?

I was thinking I could maybe just have a table like car_attributes with columns attribute_type_id, attribute_id and car_id, then a table attribute types, with attribute_type/attribute_type_id, and another table for the attribute names.

Is this a good idea? Anyone have any suggestions/resources for dealing with this type of thing? This is in MySQL if that's relevant.

# ? Jun 1, 2011 13:36

epswing: Nov 4, 2003; Soiled Meat

I think you might be describing an EAV.

In a classic EAV example, you'd have a table Cars (Id), and a table Attributes (CarId, Attribute, Value) where the Attribute and Value columns are varchars.

code:

Cars table
Id  
---
 1    
 2    
 3    

Attributes table
CarId   Attribute       Value
-----+----------------+-----------
 1      Make             Kia
 1      Model            Sorento
 1      Spoiler 
 1      Sunroof
 1      Ashtray

 2      Make             Honda
 2      Model            Accord
 2      Sunroof
 2      Ashtray
 2      Power Steering

 3      Make             Honda
 3      Model            Civic
 3      Sunroof
 3      Power Steering
 3      Power Brakes

The idea of an EAV is it turns your columns into rows. This has pros and cons. But this doesn't quite make sense for your scenario, because you want to track "the existence of a sunroof," either the car has it or it doesn't, which makes no use of the Value column. Perhaps it makes sense to put some values as columns in the Cars table, and ditch the Value column in the Attributes table. Like such:

code:

Cars table
Id   Make   Model
---+-------+------
 1   Kia     Sorento
 2   Honda   Accord
 3   Honda   Civic

Attributes table
CarId   Attribute     
------+---------------
 1      Spoiler 
 1      Sunroof
 1      Ashtray

 2      Sunroof
 2      Ashtray
 2      Power Steering

 3      Sunroof
 3      Power Steering
 3      Power Brakes

Warning: EAV has a bad rap when it comes to reporting. It may make it easier to insert data and control column-explosion, but when you pull data out, you're going to get rows where you want columns. There are other database engines called "document databases" like MongoDB, CouchDB, etc, which are theoretically better than EAV in relational databases.

epswing fucked around with this message at 16:27 on Jun 1, 2011

# ? Jun 1, 2011 14:17

a cat: Aug 8, 2003; meow.

epswing posted:

I think you might be describing an EAV.

Awesome thanks for the tip. One thing I'm still not sure of though, is that if values are just stored as varchars, what do you do when you want to list all possible values, for example, in a dropdown select box or something, that would automatically be updated when you decide to add a new possible type (ie a new model of car comes out)? I guess it's not hard to just get the set of values that exist in a column, but I was always under the impression (though I could easily be wrong) that it was good to have a table of categories for this very reason.

I've actually messed with MongoDB a little in the past and the thought occurred to me that this might be a decent thing to use it on.

# ? Jun 1, 2011 15:35

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Refresh me please - do I ever have to create an index containing just a table's primary key, or is one internally created by virtue of it being the primary key? This is for a MySQL MyISAM table.

# ? Jun 1, 2011 15:57

epswing: Nov 4, 2003; Soiled Meat

jstirrell posted:

what do you do when you want to list all possible values, for example, in a dropdown select box or something, that would automatically be updated when you decide to add a new possible type (ie a new model of car comes out)? I guess it's not hard to just get the set of values that exist in a column, but I was always under the impression (though I could easily be wrong) that it was good to have a table of categories for this very reason.

Like you said, SELECT DISTINCT Attribute FROM Attributes (which, in fact, could be a view). If Attributes is indexed, this should be fast enough for most purposes. If you want, you could keep another table around which is the result of that query, and update it once an hour/day/whatever.

Golbez posted:

Refresh me please - do I ever have to create an index containing just a table's primary key, or is one internally created by virtue of it being the primary key? This is for a MySQL MyISAM table.

I'm 99% sure prikey implies index.

# ? Jun 1, 2011 16:29

benisntfunny: Dec 2, 2004; I'm Perfect.

epswing posted:

I'm 99% sure prikey implies index.

You'd be 100% correct.

quote:

A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY. If you do not have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY.

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

# ? Jun 1, 2011 17:29

subx: Jan 12, 2003; If we hit that bullseye, the rest of the dominoes should fall like a house of cards. Checkmate.

jstirrell posted:

I need to design a database where there are two main entities, for example, people and cars for a car rental website. My problem is that each of these needs to have a huge number of named attributes. For example a car needs to have a "music player type" (ie cd/mp3/tape). It also has attributes like make/models. I've always thought the way to handle these. These are no huge deal alone, just have a "makes" table, a "models" table, whatever, then have a foreign key "make_id" in the cars table.

But then there are also a lot of many-to-many relationships like a "features" attribute that can be things like "sunroof" or "spoiler" or whatever (it can have multiple). So these would need a "feature_names" table (essentially just an id and name), and then a "car_features table (just a car_id and a feature_id. There are probably around 20 of these many-to-many relationships for each main entity, cars and people.

My problem is this table got extremely large and unwieldy fast. This is probably the largest application I've built from scratch on my own and it may be normal, but having 40 or so sets of nearly identical tables just seems stupid.

I'm not an expert in this domain (there's a lot of technical stuff I'm not mentioning) and I may be missing ways to better group the data, but in general is there any solution?

I was thinking I could maybe just have a table like car_attributes with columns attribute_type_id, attribute_id and car_id, then a table attribute types, with attribute_type/attribute_type_id, and another table for the attribute names.

Is this a good idea? Anyone have any suggestions/resources for dealing with this type of thing? This is in MySQL if that's relevant.

I don't think there's anything actually wrong with the way you are doing it. The problem is you say "nearly identical" and not "identical" - in computer terms those can be miles apart.

The only thing I can suggest is possibly creating a "standard_features" table that has all the models and the (obviously) features they come standard with. This would at least prevent you from needing to put an "a/c" feature on basically every car made in the past 10 years, as it would be listed on the standard features table for that model.

# ? Jun 1, 2011 18:56

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

This is probably impossible to answer without knowing my setup, but: I just saw a query on Navicat's monitor take 93 seconds to run. While it was still running, for a PHP web user, I copied the query and ran it in Navicat, and it took 1.5 seconds. How can it take so long for them and instant for me, especially when our load isn't high at all? It seems entirely illogical.

# ? Jun 1, 2011 21:29

subx: Jan 12, 2003; If we hit that bullseye, the rest of the dominoes should fall like a house of cards. Checkmate.

Golbez posted:

This is probably impossible to answer without knowing my setup, but: I just saw a query on Navicat's monitor take 93 seconds to run. While it was still running, for a PHP web user, I copied the query and ran it in Navicat, and it took 1.5 seconds. How can it take so long for them and instant for me, especially when our load isn't high at all? It seems entirely illogical.

Queries get cached, run faster the second time. That's what stored procedures are for.

# ? Jun 2, 2011 04:03

Adbot: ADBOT LOVES YOU

# ? Jun 11, 2024 04:25

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

subx posted:

Queries get cached, run faster the second time. That's what stored procedures are for.

But the thing is, I ran it while it was still running for the other person. Like, when the clock on the other person's query was at 45, I ran it, it finished in 1.5 seconds, but it continued to tick up to 93 for them. No rows returned, and this is before it gets sent to PHP residing on the same server so can't blame network congestion.

# ? Jun 2, 2011 05:51

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

«‹›303 »