I am doing my first database project. I have had the following query in the column of EMAIL_ADDRESS ... EMAIL_ADDRESS CHARACTER VARYING(20) NOT NULL, ... However, John Saunders uses VARYING(256). This suggests me that I have not necessarily understood the VARYING correctly. I understand it such that the le...
John uses CHARACTER VARYING in the places where I use VARCHAR. I am a beginner, while he is an expert. This suggests me that there is something which I do not know. What is the difference between CHARACTER VARYING and VARCHAR in PostgreSQL? ...
I want to insert n records into a single table. There may be many concurrent users and they may insert/update/select data from this table. What is better way to insert in a such table say 1000 records: Send a single sql query to a database with multiple inserts. This saves server to database calls, but (I not sure) locks the table unti...
This question is based on this answer. What does the following sentence mean? Finally, don't use mixed case identifiers. Do everything lowercase, so you don't have to quote them. Does it mean that I should change the commands such as CREATE TABLE, USER_ID and NOT NULL to lowercase? - It cannot because it would be against commo...
For a web application database, from a security standpoint only, what are arguments counter to the point for an sp only solution where the app db account has no rights to tables and views and only exec on sps? If someone intercepts the app db account, the surface area exposed to an attack is much less then when tables and views aren't e...
I am doing my first database project. I would like to know how you can have false as the default value for the following SQL -query ... MODERATOR_REMOVAL boolean NOT NULL ... Context CREATE TABLE Questions ( USER_ID integer FOREIGN KEY REFERENCES User_info(USER_ID) PRIMARY KEY ...
I read that there are datatypes which do encryption so passwords are secured in your database. I use at the moment varchar to store passwords. I have had the idea that I should somehow apply a SHA-512 function to the password and put that data somewhere such that the plain text password is removed. However, the datatype in Perl suggest...
I've seen a few questions on here about storing user preferences, but they seem to be referring mostly to a fairly minimal set of preferences. I'm currently working on a highly customizable web app, which will need to store a great number of preferences, and I'm struggling with how to store them. The kind of preferences I'll be storing ...
Hi, I have two queries to get related tags from a mysql database, one works, one does not, my question is: "why?" Problem: When executing the first query, the mysql server gets 100% cpu usage, and has to be restarted to function again. Query 1 (does not work): SELECT tags.*, COUNT(ct.company_id) AS count FROM company2tag ct, tags WHE...
My ERD is I use at the moment integer(2) to signal that the datatype is boolean because the program does not allow me to type boolean to the datatype -field. How can you have boolean as a datatype in the program VP-uml? [edit] The problem still persists. I see the following in my VP-uml: ...
I'm using Rails migrations to manage a database schema, and I'm creating a simple table where I'd like to use a non-integer value as the primary key (in particular, a string). To abstract away from my problem, let's say there's a table employees where employees are identified by an alphanumeric string, e.g. "134SNW". I've tried creating...
Does altering the JOB_QUEUE_PROCESSES to 0 on an Oracle DB block the Oracle jobs from being scheduled too? I found in http://download-west.oracle.com/docs/cd/A97630_01/server.920/a96521/jobq.htm some information about it, principally, the following: The JOB_QUEUE_PROCESSES initialization parameter controls whether a coordinat...
Are there other strategies available for auto-incrementing a primary key? So instead of the usual x+n where n is 1, could n be some other number such as 5? Or perhaps a random number but such that the next ID is still unique? I'm not sure what the uses would be, or even if it's a good idea, I'm just curious. ...
I'm gathering a vector of time/value pairs, in this case I/O latency versus time for a server. I'd like to store them in a MySQL database that is queryable by date, server and other metadata. Examples: I want be able to query the I/O latencies from 1:00 PM to 3:00 PM for the server Atriedes on August 19th, 2007. I want to also be able...
In MySQL, is it generally faster/more efficient/scalable to return 100 rows with 3 columns, or 1 row with 100 columns? In other words, when storing many key => value pairs related to a record, is it better to store each key => value pair in a separate row with with the record_id as a key, or to have one row per record_id with a column f...
I have a datamapper class that will eager load any property that does not have a Lazy attribute attached to it. I have two entities, State and Country, Country has an inverse relationship to state in that it contains a list of all the states of that country and State has a forward relationship to Country in that it has a property Countr...
I have a script which generates queries in the following fashion (based on user input): SELECT * FROM articles WHERE (articles.skeywords_auto ilike '%pm2%') AND spubid IN ( SELECT people.spubid FROM people WHERE (people.slast ilike 'chow') GROUP BY people.spubid) LIMIT 1; The resulting data set: Array ( [0] => Ar...
i have two databases name (trial1) and (trial2) and table name studmast in trial1 and trial2. I want to transfer data of studmast in trial1 to studmast in trial2. can anybody help me?? ...
To preface this, I'm not familiar with OLAP at all, so if the terminology is off, feel free to offer corrections. I'm reading about OLAP and it seems to be all about trading space for speed, wherein you precalculate (or calculate on demand) and store aggregations about your data, keyed off by certain dimensions. I understand how this wo...
Hello. Sorry, I couldn't provide a better title for my problem as I am quite new to SQL. I am looking for a SQL query string that solves the below problem. Let's assume the following table: DOCUMENT_ID | TAG ---------------------------- 1 | tag1 1 | tag2 1 | tag3 2 | tag2 3 ...