Hot answers tagged

11

If you create a database without specifying a file size, the size is mapped from model. You can see what this is by running: EXEC model.sys.sp_helpfile; -- on my system, size of primary data file -> 3264 KB Now, if I create a database just using CREATE DATABASE, I get an exact replica of those files. CREATE DATABASE [floob1]; GO EXEC floob1.sys....


11

Read this paper: Multi-Tenant Data Architecture. It goes at length on the pros and cons of 3 possible approaches (tenant ID in each table, schema per tenant, database per tenant). This approach is called "Shared database, Separate schemas" and is a valid approach. Whether it is best for your case, I'm not in position to make that call. This is what the paper ...


9

A functional dependency is exactly what the term implies - the output of the function is always determined by the input. If for example we have a function f(), and provide variable x, and we always receive output y, then y is functionally dependent on x. You can think of this like a simple graphing function 2x + 1 = y Plugging some sample values into the ...


8

What happens when a new programmer arrives and has to write an app against that db? They don't know that field x has to be NOT NULL. Another programme might assume that all field x's are NOT NULL for performing counts say, but some now are NULL because of the new programme, leading to inconsistent and difficult to trace errors. IMHO it is always best to ...


6

The least work would be to put a language code in each table. The problem then is there is no way to show that, say, post 612 (English) is a translation of post 943 (Spanish). To achieve that level of cross-reference it will be necessary to separate the post's existence from its content. There will be these tables: Language Language_id Name Post ...


6

This simple question has caused an unusual amount of debate, and I feel compelled to quote an authoritative source. The database stores all data in tables that are related to one another. Relationships are established between tables such that data is shared and duplication is minimized. A table space is a storage structure containing tables, indexes,...


6

As already cited by others in comments, adding NOT NULL to your table specification can improve in a significant way the performances of your queries (in addition to the very good methodological reasons stated in another answer). The reason is that the query optimizer, knowing that a column cannot have a NULL value, can exclude special tests for such values,...


5

You are on the right track here. Somethings to consider: Users Likely a User can have multiple resumes. Job candidates often tailor a resume to a job opening and want to keep all of those. This means you will need a User table. Having a user table also gives you a place to store attributes of the user such as their name. Sections A resume has lots ...


5

The documentation on psql explains: Whenever the pattern parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path — this is equivalent to using * as the pattern. (An object is said to be visible if its containing schema is in the search path and no object of the same kind and name ...


5

SSDT is comparable to Liquibase/Flyway as it does what they do but by taking a different approach. With SSDT you have the development environment so you get things like go to definition, find references and intelli-sense as well as the ability to compile a project into a dacpac and then deploy that dacpac to a database. The SSDT way (and redgate sql compare ...


5

Without knowing exact and whole requirements we cannot design a complete data model. Let us assume the following business rules, it is based on your question. Each Customer can request for one or more Room. Each Room can be requested by one Customer Each Admin can assign one or many Room. Each Room must be assigned by one Admin. Each Room must be belongs ...


4

No, no, no, you should definitely not do this. It is the responsibility of the object creator to grant the necessary privileges. (Although this is a common request, and it would be nice to finally have built-in support for this.) Since you are on 12c, forget SELECT, and grant READ. SELECT is not a read-only privilege. With SELECT granted, the grantee can ...


4

Triggers run, by default, under the security context of the principal who caused the trigger to fire. In order to change this behavior, you'll need to create the trigger using the WITH EXECUTE AS OWNER clause. Below is an example which shows how that works. WITH EXECUTE AS OWNER allows the trigger to run in the security context of the database owner, ...


4

There are three parts to this Question, so let's start with the "Block on Data Loss" option, dropped Tables, and then dropped Columns. "Block on Potential Data Loss" Option If you enable the "Block incremental deployment if data loss might occur" option: on the "Debug" tab of "Project Properties" using the /p:BlockOnPossibleDataLoss=True command-line ...


3

Which of the two options is the best? The relational design is clearly superior to the hierarchical design used in the NoSQL example. A database schema designed using relational principles does not favor one access path over another. Each table represents a real world entity type and through the use of relational algebra queries of arbitrary complexity ...


3

Owned by more than one user: no Accessible by more than one user: yes To give a user (or role) access to the tables in a schema you can use something like this: grant all on schema foobar to role_name; grant all on all tables in schema foobar to role_name; grant all on all sequences in schema foobar to role_name; grant all on all functions in schema foobar ...


3

If you are looking for significant performance improvements to dnoeth's answer, consider using a native C-function and creating the appropriate operator. Here is an example for int4 arrays. (A generic array variant and the corresponding SQL script). Datum _int_sequence_contained(PG_FUNCTION_ARGS) { return DirectFunctionCall2(_int_contains_sequence, ...


3

This is taken from the MySQL documentation: Changes in MySQL 5.7.1 (2013-04-23, Milestone 11) ... If a column is declared as NOT NULL, it is not permitted to insert NULL into the column or update it to NULL. However, this constraint was enforced even if there was a BEFORE INSERT (or BEFORE UPDATE trigger) that set the column to a non-NULL value. ...


3

Let's suppose the institution does send me a listing of all the tables and their columns. Even though I don't have the actual data, I can now attempt social engineering attacks to convince insiders into giving me whatever data I want. The request would sound much more legitimate, because I could ask for very specific pieces of information using the actual ...


3

Strictly speaking, _id fields are as unique as you make them. As you have discovered, the default values for _id are ObjectIDs but you can populate the field with any data you wish. Therefore, for example, you could use a UUID as I have done in this sharding example and then the chances of a collision across collections would essentially be whatever the ...


3

I have used the following style of schema for multi-lingual support: +---------------+ | CATEGORIES_B | +---------------+ | category_id | | category_code | | ... | +---------------+ | | +-------------------------+ | CATEGORIES_T | +---------------+ +----------------------...


3

A few things spring to mind. The patient_info table should be called patient. The (my) patient table should contain patient_name. Every table should have a Primary Key called table_name_id and not just id as you have (makes debugging difficult - which "id" is causing the problem + problems of ambiguity of column names). The medical_history table should ...


3

One way this happens is by having a Login that has the CONTROL SERVER server-level permission (hence does not need an actual USER in order to access a database) but not in the sysadmin fixed server role (as then there is an implied default schema of dbo) create an object without explicitly specifying the schema to put it in. According to the MSDN page for ...


3

No, that is not possible. You can wrap those names in square brackets, but that is not recommended, since it will likely cause a lot of confusion. For instance: CREATE SCHEMA [hr.report]; SELECT statements against objects in the schema would then look like: SELECT * FROM [hr.report].SomeTable;


3

You cannot revoke the public pseudo-role from users. Every user always has public. You can revoke privileges from the public role. The first question would be whether these privileges were granted by default by Oracle, in which case revoking them might prove difficult. It might, for example, break upgrades and patches and/or be re-granted by future ...


2

what factors should I be considering when trying to decide between storing XML in an xml column vs. a varchar(MAX) column The factors are: The XML type is queryable / parseable through XQuery expressions, including being able to use FLWOR Statement and Iteration Data in XML variables and columns can be modified inline using XQuery expressions via XML DML....


2

It mostly depends on your needs, because it is a common fact that standalone tools feature more options and are faster. I couldn't find the feature comparison list between Visual Studio and Redgate, but I ran into a post that compares the performance of 2 SQL schema comparison tools - ApexSQL Diff and Redgate SQL Compare: http://blog.apexsql.com/apexsql-...


2

Beginning On postgres 9.3, One trick you can use in postgres to get the exact sql of informational command (such as \d, \du, \dp, etc) in psql is by using a transaction. Here's how the trick goes. Open one postgres session, then type your command : begin; \dn+ While the transaction still running, open another postgres session, and query the ...


2

What you had originally was a correct syntax - for tables, not for schemas. As you did not have a table (dubbed 'relation' in the error message), it threw the not-found error. I see you've already noticed this - I believe there is no better way of learning than to fix our own mistakes ;) But there is something more. What you are doing above is too much ...


2

There is a spectrum of options open to you, depending on how much normalisation you desire, how much denormalisation you're prepared to live with, the use cases you have currently and what future functionality you reasonably expect to support. The most trivial implementation would be to treat your JSON as a blob and store it accordingly, in a VARCHAR column....



Only top voted, non community-wiki answers of a minimum length are eligible