Tag Info

Hot answers tagged

3

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


3

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

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


2

It seems that you are involved in a scenario that requires the creation of an auditable database. You may find related information by searching for the terms temporal databases and database history tables. Yes, there are cases in which it is paramount to keep track of the updates that an entity suffers over time, and there are other cases in which changes ...


2

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


2

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


2

Off guess you are missing the connect permission. I'd always thought that was created with the user but there may be conditions where it isn't added. Try running USE [MyDatabase]; GO GRANT CONNECT TO [MyDomain\MyAccount]; You could also try changing the default schema of [MyDomain\MyAccount] ALTER USER [MyDomain\MyAccount] WITH DEFAULT_SCHEMA = ...


2

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


1

You can easily find the subsequence when you cast the arrays to strings and replace the curly brackets with commas: translate(cast(sequence as varchar(10000)), '{}',',,') {1,3,17,25,377,424,242,1234} -> ',1,3,17,25,377,424,242,1234,' Do the same for the array you're searching for and add a leading and trailing %: '%' || translate(cast(searchedarray ...


1

I would handle this scenario by means of a (super)type-subtype relationship. You may find this answer valuable as an introductory reference. Approach for this particular scenario If I understand your specifications properly, the following assertions are relevant in your business context: A Party[1] is either one Group or one User. A User (playing the ...


1

No, ODBC cannot set the schema. ODBC is (in Wikipedia-speak) "a standard programming language middleware API for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems." If you want an array of schemas, they need to be created in the database before they are used. If you ...


1

The answer to both of your questions is really the same. You are using a script generation tool. It does what it's told to do. Typically the second command is only needed if you are doing a WITH NOCHECK on the first one. However it's included because the settings for the script generation component of SSMS told it to include it. Here is a post I did on ...



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