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


10

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


8

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

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


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


6

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


6

TL;DR The query Visual Studio is executing is being blocked and the query times out. Turns out I've managed to work this out for our situation: timing out due to blocking! I ran a profiler trace with a filter on application name of Microsoft SQL Server Data Tools, Schema Compare to capture the Visual Studio schema compare events. It would reach a ...


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


5

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


5

What you are describing is the EAV (Entity Attribute Value) model which most database professionals would run a mile from. It's also sarcastically called the OTLT (One True Lookup Table) and is a classic novice mistake. Your hunch is correct! Here (and here) is the opinion of Joe Celko (a veteran SQL programmer who is/was a member of the SQL standards ...


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

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

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


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

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 think you are over complicating this. Grants and roles govern access by users not what table space and data files where the data is located in. Yes, backup, recover and export are much easier if schemas have their own table space but that doesn't seem to be your question. Separate your business domains by user/schema. Give each user/schema their own ...


3

There is a very good aggregation of resources on the following link http://www.databaseanswers.org/data_models


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

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


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

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


3

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


3

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


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

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


2

Can't really add much to @RemusRusanu 's answer - maybe this should be made into a community wiki on this issue - it seems to crop up regularly in various shapes and forms. There is, as Remus points out, no single answer to this question - basically, it's a series of compromises which boil down to business decisions less than technical ones. For example, ...



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