Hot answers tagged

11

This happens when not having USAGE privilege on any of the schemas of search_path. By default the pseudo-role public (all users) has this privilege on the publicschema, so this error happens only after revoking it explicitly with: revoke usage on schema public from public; This is required when it's not desirable that people peek into other people schemas,...


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

It seems that you are involved in a project that requires the creation of a temporal database. You may as well find related information by searching for the terms auditable databases and database history tables. I deem this exceptional Stack Overflow answer as the top material with respect to these topics. In such post, @PerformanceDBA models an auditable ...


6

So what I am looking for is a way to store a large number of activities that have almost no fields in common in a way that makes reporting easier. Not enough rep to comment first, so here we go! If the primary purpose is reporting and you have a DW (even if it isn't star schema) I'd recommend attempting to get this into a star schema. The benefits are ...


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 ...


4

If I were you I would make sure to separate all of the data. Is this going to be completely controlled in the database, or is there going to be an application? If it is an application I would have a users table and permissions tables for the people using the application. I would do a separate a customers table for your customers. I agree with the ...


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

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

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

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

I will use pseudo code to illustrate; you can adapt to your DBMS's specific syntax. Join availability to transaction on room.id and also on the overlapping intervals i.e. Availability.from <= TimePartOf(transaction.checkout) And availability.til >= TimePartOf(transaction.checkin) If, for example, a room had different rates in the morning and ...


3

I have done one structure that may help you. room. It's the main room configuration where you can add any value that refer to the rooms. room_status. It's the room status where you could know which is the room actual status. transaction. Its your main table, here you're going to storage all the activities of the rooms. user. Here you can delimit which ...


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

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

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 ...



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