|
One other advantage of sequence-driven integer synthetic keys is that you know for sure the order in which rows are inserted. This is not always important, but it is occasionally very important.
|
# ? Apr 27, 2010 04:33 |
|
|
# ? May 26, 2024 14:38 |
|
I'm looking for a database solution and I was wondering what your thoughts are... I work for a company that does product testing, and each time our product is tested, large amounts of data is produced. By large, I mean the equivalent to 10,000 - 100,000 rows of data per test. (We are testing planes and recording information based on every instant, so there is a ton of data). So, here are the specs of this database: 1) It will have an enormous number of rows for some tables, lets just say an indefinite number. 2) for our database, only about 3-5 users, so not a lot of demand on it* 3) Almost every other day, certain tables will be dumped and downloaded.. so the speed of doing queries should be reasonably fast. 4) It needs to work with Matlab What system do you suggest? Oracle, MS SQL Server 2005, MySQL good enough, etc? Comedy Access 2007 option? *The reason for only 3 users is because our database is locked down and thus only certain data can be transmitted by manually dumping. The open version of this database will be Oracle 10g, but that is a separate server. The open version will have lots of demand, 1000 of users using it constantly, so that has to be a superhero level server. What database do you think will work for us here? If Oracle, what kind of server or would a software solution work? (We currently have very powerful, but generic server computers in our area). What kind of price are we looking at? (I called up Oracle and they claimed they have no pure software version of their database and wanted upward of $30k... I could have sworn there is a software version of Oracle..)
|
# ? Apr 27, 2010 04:36 |
|
cannibustacap posted:I'm looking for a database solution and I was wondering what your thoughts are... Oracle is (imo) really the only viable DB instance implementation to use for large enterprise solutions. It doesn't sound like you need that scale of things (10-100k rows is not a Lot Of Data) and Oracle is loving Expensive so I'd go for postgres or mysql for your app. YMMV. No loving clue on the Matlab thing.
|
# ? Apr 27, 2010 04:44 |
|
It seems like you need a lot of raw power but not a lot of complexity or special features. I would lean towards a free database solution rather than Oracle; take the money hats you'd spend on Oracle licensing and spend it on speedy hardware with a fast disk array. So that gets us down to postgresql vs. mysql; I know nothing about MS SQL Server, other than it's probably cheaper than Oracle because everything is cheaper than Oracle. What kind of queries will you be using to dump your data? If you will need to join large numbers of rows from multiple tables together, go with postgresql, because MySQL does not yet support hash joins as far as I know. But if all you're going to do is grab a day's worth of data from each table individually and dump it to the 1000-user Oracle database, MySQL can probably do just fine. As for Matlab, if you just need to input a bunch of data to it, I would hope that it can work with simple csv files which any db product can dump; in fact it would be pretty appalling if it couldn't.
|
# ? Apr 27, 2010 05:26 |
|
Markoff Chaney posted:The other thing it boils down to is if you have to add a synthetic PK *later* instead of just adding it *now* it's going to be a horrible long running script to do so instead of a quick little bit of DDL out the gate. Also while I was in college (and working for campus IT) my school switched away from using SSNs as student identifiers to a synthetic ID number. It took about 2 years, during which time the DBAs did almost nothing else.
|
# ? Apr 27, 2010 17:04 |
|
MoNsTeR posted:Also while I was in college (and working for campus IT) my school switched away from using SSNs as student identifiers to a synthetic ID number. It took about 2 years, during which time the DBAs did almost nothing else. Also, a corollary lesson: don't ever use SSNs for unique identifiers. They're not unique and not everyone has one.
|
# ? Apr 27, 2010 17:08 |
|
Are there any apps where you can automatically generate at least a basic PHP web interface to an existing SQL database you have rights to? Any for OS X? I'm working on an internal tool for work, and it doesn't need to be pretty, we just need to be able to easily control how information is dumped to screen. Being able to edit and save to the DB would be valuable as well. Edit: Ok, different question, and this is hopefully really simple. How do I verify that a record I am inserting is not identical to another record in the table, except for the unique auto-incrementing ID? I like turtles fucked around with this message at 03:15 on Apr 28, 2010 |
# ? Apr 27, 2010 23:49 |
|
I like turtles posted:Ok, different question, and this is hopefully really simple. This depends somewhat on - what you want to happen if the record is indeed identical - what the table schema is. Best case scenario: - if the record is identical, you want to not insert a new record at all - all of the columns in the table are NOT NULL - the number of columns in the table is not excessive In this case, you just add a UNIQUE KEY on all of the columns bar the ID column. Why are those three things important: - If there's a UNIQUE KEY and you try to insert a duplicate row, the insert will not happen. (You will get an error message back, unless you use INSERT IGNORE (may be MySQL only).) - If there are nullable columns in the table, the RDBMS will permit rows that are duplicates except for placement of NULL values - If there are a lot of columns in the table, a UNIQUE KEY on them might be unwieldy for your DBMS to manage. What you could do if your design does not satisfy those three things: - Check in application logic that the record you are inserting does not match another in the table
|
# ? Apr 28, 2010 13:18 |
|
Hammerite posted:This depends somewhat on I ended up doing the last option, as unique keys on a per column basis wouldn't pull off what I needed, all of the following are valid, but unless there's a way to have a unique index cover multiple columns I guess check-at-insert is probably easiest: code:
|
# ? Apr 28, 2010 16:44 |
|
I like turtles posted:I ended up doing the last option, as unique keys on a per column basis wouldn't pull off what I needed, all of the following are valid, but unless there's a way to have a unique index cover multiple columns I guess check-at-insert is probably easiest: Having a unique key over 3 columns is no problem, my thing about not having too many columns was just to note that if there are, like, 50 columns, it probably wouldn't make sense to try and put on a unique index. In fact, checking I see that MySQL does not permit an index to be more than 16 columns. For a given storage engine there might also be a limit on the byte length of an index. You could add a unique index on your table using (assuming MySQL) ALTER TABLE `TableName` ADD UNIQUE INDEX `TableName_uk` (`Col A`, `Col B`, `Col C`)
|
# ? Apr 28, 2010 16:53 |
|
I think you misunderstood me - when I said you could "put a unique index on all the columns", what I meant was a siongle unique index, rather than individual ones for each column.
|
# ? Apr 28, 2010 16:56 |
|
Depending on your table design, normalization and functional dependencies shouldn't the primary key already be sufficient to set the uniqueness of records? Then again if you don't have the mindset of multi-column indices or have an excel-style 'database' this probably doesn't mean much to you.
|
# ? Apr 28, 2010 20:18 |
|
RoadCrewWorker posted:Depending on your table design, normalization and functional dependencies shouldn't the primary key already be sufficient to set the uniqueness of records? We literally just had this discussion at the end of last page/start of this one.
|
# ? Apr 29, 2010 00:38 |
|
Stupid question here but TOAD is throwing errors at me where i don't think it should First one is "Identifier is too long" and the 2nd is "Invalid identifier" The piece of code it complains about for the first time is this bit code:
code:
Chumpington fucked around with this message at 10:28 on Apr 29, 2010 |
# ? Apr 29, 2010 10:01 |
|
You need at least one column in a create table statement?
|
# ? Apr 29, 2010 11:00 |
|
Victor posted:You need at least one column in a create table statement? Oh sorry. Yes there was stuff in there,it was just the names it was complaining about though. edit: here is both bits code:
|
# ? Apr 29, 2010 11:05 |
|
Have you tried quoting the names with backticks? Also, your second CREATE TABLE has too many commas. I forget, does MySQL allow trailing commas after the last column/constraint definition?
Victor fucked around with this message at 11:19 on Apr 29, 2010 |
# ? Apr 29, 2010 11:17 |
|
Victor posted:Have you tried quoting the names with backticks? Also, your second CREATE TABLE has too many commas. I forget, does MySQL allow trailing commas after the last column/constraint definition?
|
# ? Apr 29, 2010 11:21 |
|
Victor posted:Have you tried quoting the names with backticks? Also, your second CREATE TABLE has too many commas. I forget, does MySQL allow trailing commas after the last column/constraint definition? I'm not certain about CREATE TABLE statements specifically, but I would guess not, since there are other situations in which it doesn't allow trailing commas after the last item in a list.
|
# ? Apr 29, 2010 12:02 |
|
Does anyone make a tool that checks MySQL for syntax errors and provides, ya know, good error messages? The MySQL 5.x errors when I use the CLI tool are just horrific! I can't believe the foolishness that is "comment out most of the code and see if it accepts, now comment out a little less". That'd probably be my biggest reason not to use MySQL; I want to spend my time writing code, not troubleshooting things a computer should be able to do, trivially. (nerrrrd rage)
|
# ? Apr 29, 2010 13:43 |
|
I'm trying to debug this, and maybe I'm dumb but I don't see anything obviously wrong with it.code:
|
# ? Apr 30, 2010 16:32 |
|
Does it work without the OUTPUT clause?
|
# ? Apr 30, 2010 16:56 |
|
I haven't tried it - other parts of the code need that clause, the whole point of the insert statement being this complicated is to retrieve the guid for the new entry. I'm thinking of just simplifying the whole mess by generating the guid in the front-end code instead.
|
# ? Apr 30, 2010 18:42 |
|
You should be able to run a syntax check on it without executing it.
|
# ? Apr 30, 2010 18:44 |
SQLite question: I have a ~100MB some_data.db3 and I want to use ATTACH DATABASE to hook up with a 2GB db3 file to do some cross-database joins. Does the ATTACH hurt the performance of queries that only use tables in some_data.db3?
|
|
# ? Apr 30, 2010 23:47 |
|
I need to get up to speed on programming for SQL Server 2008 with regards to SQL basics, triggers, stored procedures, and functions (UDFs). My current database knowledge extends to basic select/insert/update/delete queries and basic joins, from writing hobby web projects in MySQL. Can anyone recommend any good or well known books on the topic which I could study? Has anyone read Microsoft SQL Server 2008 T-SQL Fundamentals and what did you think?
|
# ? May 4, 2010 04:28 |
|
I want to duplicate a production server onto an internal development server (both running MSSQL 2005). However, the production environment is way too big and not really necessary. Is there any way that I can : - Drop just the table structure of the production server onto the dev server AND - Somehow do something like '* TOP 1000' per table so I have some but not all data from the production environment? EDIT-ignore, found a better way Scaramouche fucked around with this message at 21:27 on May 4, 2010 |
# ? May 4, 2010 21:15 |
|
Oracle question: Is there a way to automatically set linesize, pagesize, or serveroutput, or does that have to be manually done after logging in? I wish I could create a trigger to fire after login to do that sort of mundane stuff, or somehow set custom default values.
|
# ? May 5, 2010 06:29 |
|
Malfeasible posted:Oracle question: You can create a login.sql (or glogin.sql) file that will be automatically parsed by SQL*Plus. http://www.orafaq.com/wiki/SQL*Plus_FAQ#Can_one_run_commands_when_SQL.2APlus_starts_up.3F
|
# ? May 5, 2010 13:25 |
|
cannibustacap posted:I'm looking for a database solution and I was wondering what your thoughts are... We're in the process of testing a postgres variant called Greenplum, which is incredibly faster than our Oracle setup and much, much cheaper. That said, everything is cheaper than Oracle. Obviously it's going to cost a bit of hours if we were to convert all our Oracle stuff to Greenplum, but it might be worth looking into for your situation. Scripts that take hours to run in our Oracle setup takes minutes on just one node of Greenplum. And to make it faster, all you have to do is add more nodes. It's not as forgiving as Oracle is, it doesn't like correlated subqueries and there's some weird distribution key fields but it's much easier to start with it than it is to convert to it.
|
# ? May 5, 2010 22:34 |
|
Had a more operational question. We've got two MSSQL 2005 databases in a transactional replication relationship, one publishes (let's say S1) and one subscribes (let's say S2). We're noticing a big chunk of trunc info that's taking up disk space, however we know from experience we can't just run truncuate on the publisher and expect the subscriber to just accept the changes gracefully. Is there a way to truncuate replicated databases, or do we have to break replication, truncuate both, and reconnect?
|
# ? May 7, 2010 18:20 |
|
Factor Mystic posted:I need to get up to speed on programming for SQL Server 2008 with regards to SQL basics, triggers, stored procedures, and functions (UDFs). My current database knowledge extends to basic select/insert/update/delete queries and basic joins, from writing hobby web projects in MySQL.
|
# ? May 8, 2010 01:15 |
|
Using sql only how could I extract just the birthdate from a serialized array using substring or index of? a:16:{i:0;s:10:"1985-12-10";i:1;i:1;i:2;i:1;i:3;i:1;i:4;i:0;i:5;i:1; i:6;i:1;i:7;i:1;i:8;i:1;i:9;i:0;i:10;i:1;i:11;i:0;i:12;i:0;i:13;i:0;i:14;i:0;i:15;i:0;} select birthdate from profile_users I want to manipulate the birthday field until I just get that date. I tried: SELECT substring( birthdate, 17, 17 ) AS birthdate FROM profile_people but that still leaves me with 1955-08-13";i:1;s when I want 1955-08-13 Edit nvm: where type ="stupid" is correct! SELECT substring( birthdate, 17, 10 ) AS birthdate does the trick. Flatlander fucked around with this message at 22:19 on May 10, 2010 |
# ? May 10, 2010 22:07 |
|
Flatlander posted:Using sql only how could I extract just the birthdate from a serialized array using substring or index of? well that likely just a small mental error on your part. Substring works by ( yourstring, starting index, length) so code:
|
# ? May 10, 2010 22:20 |
|
Yes, thx, that works fine and I made it a bit better: select substring(birthdate, locate('"', birthdate) + 1, 10) from profile_people
|
# ? May 10, 2010 22:37 |
|
How come when I use the MySQL "export" tool, it screws up unicode characters? I'm using the MySQL Administrator tool to back up my database. When I look through the backup, I see rows like code:
The column in which the data is stored is VARCHAR(750) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL. I've checked this by looking at the CREATE TABLE statement. Furthermore, I feel confident that the text isn't stored mis-encoded, because I have a PHP script that displays it correctly (output straight from the database), and the PHP script has charset=utf-8 specified in its content-type meta tag (and Firefox correctly identifies it as UTF-8 encoded). EDIT: Never mind. I did some testing with a dummy table and found that the "restore" functionality in MySQL Admin doesn't even appear to work (it claims to have successfully restored data, but doesn't actually do anything). The export/import functionality in PHPMyAdmin exhibits the same peculiar character set issue in the dumped file, but it seems to arrive correctly in the database upon doing a restore, so perhaps it doesn't matter. I'm just going to write MySQL Administrator off as being bloody useless and continue to use PHPMyAdmin for this stuff. Hammerite fucked around with this message at 22:57 on May 13, 2010 |
# ? May 13, 2010 22:03 |
|
Hammerite posted:How come when I use the MySQL "export" tool, it screws up unicode characters? Your connection via MySQL Admin probably wasn't using a UTF8 connection. Yes Mysql is very stupid for things like this where the connection language will override whats in the tables for exports.
|
# ? May 14, 2010 01:35 |
|
This is gonna be the stupidest question ever, but I feel like the biggest retard right now ... I have a many-to-many relationship between two tables, Pizza and Topping. Obviously a Pizza can have many Toppings and a Topping can be on many Pizzas. I have a bridging table, PizzaToppings, which lists which Pizzas have which toppings. The relationship looks like this: code:
It looks like this: code:
Now, I want another listbox whose query will list each Pizza's ID number as well as the ID number and Name of every topping which is NOT on the Pizza (the idea being that I can put arrow buttons between the two listboxes allowing the user to add and remove toppings from a pizza). Do excuse my complete idiocy but how would I achieve this? It doesn't seem to be as simple as saying "NOT" in the WHERE clause. I feel like such an idiot ... Stobbit fucked around with this message at 07:36 on May 17, 2010 |
# ? May 16, 2010 17:02 |
|
code:
|
# ? May 16, 2010 17:46 |
|
|
# ? May 26, 2024 14:38 |
|
Stobbit posted:This is gonna be the stupidest question ever, but I feel like the biggest retard right now ... That really sounds something i would do application side rather then at the db level.
|
# ? May 16, 2010 19:38 |