Tag Info

Hot answers tagged

8

As you've identified, storing the price on the order makes the technical implementation easier. There are a number of business reasons why this may be beneficial though. In addition to web transactions, many businesses support sales through other channels, e.g.: Over the phone Sales agents "on the road" At a physical location (e.g. shop, office) In ...


7

The basic concept is actually quite simple: you generate a script from sys.objects and sys.schemas that builds ALTER SCHEMA TRANSFER statements. So for example, you have three objects in the dbo schema, and you want to move all of them to the blat schema: Table: dbo.foo Table: dbo.bar View: dbo.vFooBar The following code: DECLARE @sql NVARCHAR(MAX) = ...


6

Always start by identifying your natural keys, in this case key. If these are too complex (too many columns) or not stable enough (changes to often), consider adding a surrogate key (such as the ID column you mention). The criteria I use for keys are: - unique - stable - irreducible - complexity - familiarity In many situations there will be a conflict ...


6

Your second option is more flexible, but I'm not sure why you're worried about "tons more tables". Usually this would be done with a single table: contact_types ------------- id (PK) name contact_details --------------- id contact_type_id (FK to contact_types.id) value organization_contacts --------------------- id (PK) contact_detail_id ...


5

Option 4: Class Table Inheritance This is a design technique used to implement a class/subclass situation. In this situation, attributes often apply only to one subclass, and not to all rows in the table. Visit the tag of the same name over in SO to see a bunch of relevant questions and answers. This has the advantage eliminating NULLS (mostly), ...


4

Do I understand correctly: you scripted tables only, created the database manually and then ran the script to create the tables? You have to either script the whole database and create the new database using that script (change the DB name if you'll execute it on the same instance) or at least include schemas in the list of database objects to script ...


4

You don't need to drop the dependent views, but you do need to alter them to (temporarily) not be schemabinding. If any of them are indexed, this means you will need to re-create the indexes. SQL Server doesn't have some weighting system for schemabinding: you can't change the object. Period.


4

I personally like the second option best. This is a fairly common way to solve this problem. Consider adding a "default_contact_type_id" column to your organization table for quicker lookups and/or reporting. Option 3: For the sake of completeness, you could add an XML column to the organization table calles simply "contact_information". An XML blob can ...


4

There is more than one school of thought concerning the naming of columns or fields. One school of thought says that the name should express the intent of the data being conveyed even if that name is separated from the context where it is found. Thus, a name like post_date or another name like comment_date tells the reader not only that this is a date, ...


4

Execute this for all of the tables: alter schema2 transfer schema1.table1; To programmatically get the statements to execute: declare @SourceSchemaName sysname, @DestinationSchemaName sysname, @AlterStatements varchar(max); set @SourceSchemaName = 'NewSchema'; set @DestinationSchemaName = 'HumanResources'; set @AlterStatements = ''; select ...


4

It looks like you'll need to change the user's default database, as it appears this is currently set to DB1, which no longer exists. exec sp_defaultdb @loginame='user1', @defdb='DB2' OR ALTER LOGIN <login_name> WITH DEFAULT_DATABASE = <default_database>


3

Why are you using ##global temp tables instead of #local? It is possible that when you are changing the procedure there is already a global temp table in existence with the same name - they're global so it could have been created by anyone. Use a local temp table and that problem will go away. Also, please don't use the sp_ prefix. Stored procedures ...


3

Aha! Thanks to @ypercube's comment about "inheritance", I managed to make a logic/context leap and found some concrete examples that give the name "Polymorphic Associations" to this kind of schema design. http://www.slideshare.net/billkarwin/practical-object-oriented-models-in-sql (slide #24) Under what conditions are polymorphic associations used? What ...


3

how to create "empty" or "blank" databases I would use pg_dump with the --schema-only option to create the dump for an empty database: pg_dump mydb -s > /mypath/myfile.sql Using Linux, you could also pipe the output to psql and create a new, empty database in the same (or any other) database cluster right away. Example for the same cluster: ...


3

SERIAL is an alias datatype for an INTEGER type column with a defult coming from a sequence. If you defined your table as having a SERIAL type column, then you need to get the actual name of the sequence from the table. Have a look at the output of \d your_table and retry your GRANT command with the proper sequence name. If you did name your sequence ...


3

Your design looks a bit like the "supertype/subtype" pattern. Search for that and for "table inheritance". It needs quite a lot of work to be able to enforce integrity constraints though. You are missing a generic Fruit table (that's the "supertype") and a FruitType table to store the alllowed fruit types: FruitType fruit_type PK Fruit fruit_type ...


3

Site1 and site2 can both be FKs, linked to the ID_site PK. This works on any rdbms that i'm aware of. Example Query: SELECT CT.site1, CT.site2, S1.site_description, S2.site_description FROM circuit_table AS CT INNER JOIN site_table as S1 ON CT.site1 = S1.ID_site INNER JOIN site_table as S2 ON CT.site2 = S2.ID_site


3

I did a presentation about the various solutions to support custom fields: Extensible Data Modeling with MySQL. The most straightforward option for greatest compatibility with SQL, including constraints and data types, is just to add columns as needed. But I would caution to not let users do this, instead let them submit a request and the DBA can perform ...


3

You can use Vertabelo. Vertabelo is an online database designer working under Chrome developed by company I work for. Vertabelo focuses on online collaboration in the visual database schema creation area. The most important feature of this tool is that it allow to share database models across the team and collaborate on them via web browser. It also allow ...


3

The below sample structure illustrates how you can do the TasksTags table most efficiently. The Tasks table enforces unique task names. The Tags table enforces unique tag names. The TasksTags table joins these together allowing any combination of Tasks and Tags. USE tempdb; CREATE TABLE dbo.Tasks ( TaskID INT NOT NULL CONSTRAINT PK_Tasks PRIMARY KEY ...


3

Assuming destination schema is empty: DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N' ALTER SCHEMA Schema2 TRANSFER Schema1.' + QUOTENAME(t.name) + ';' FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE s.name = N'Schema1'; PRINT @sql; -- EXEC sp_executesql @sql; If the destination schema is not empty, ...


2

Good start so far. Here are some notes: a movie can be released on different dates in different cities please add your primary keys, so we can see them are images for movies? people? why no order column? movies schedules are more complex than you've got so far. You will want to calculate show times based on some recurrence rules (see stackoverflow for ...


2

It may end up costing more in storage, but I prefer to house all relevant details of the sale with the transaction itself, so that if for whatever reason our audit trail gets broken, or an administrator overrides the safeties in place, the details of the sale like: currency used, unit-price, qty, taxes applied and what value they came to, etc. are all ...


2

The basic concept is to use GRANT/DENY Schema Permissions. You can efficiently manage permissions by creating a role and then adding members to it. Below is an example that will explain you in detail use master go --Create Logins CREATE LOGIN UserA WITH Password='UserA123'; go CREATE LOGIN UserB WITH Password='UserB123'; use AdventureWorks2008R2 go ...


2

yes you can, just be sure that the transactions are defined by the uppper layer - the client. A transaction has to be Atomic, so has to succeed (and commit) or has to fail (and rollback).


2

I will add some practical points that I have seen. Products are transient. What they may signify today, may not be the same as what they used to signify an year back. The same sku code (and hence the product_id), might refer to different variant/kind of the product at different stages. Not everyone understands all the concerns at hand; hence a user may ...


2

Connect to the psql command --> psql --u {userName} {DBName} then you can type the below command to check how many schemas are present in the DB DBName=# \dn Else you can check the syntax by the below steps easily- After connecting the the DB, press DBName=# help You will get the below options- You are using psql, the command-line interface to ...


2

All current RDBMS' tables can have CONSTRAINTS on columns. These constraints are checked every time data is inserted into the table. It can also check data against other tables. We know that each Plate Type has certain number of Rows and Columns. We can enumerate all Rows and Columns for each Plate Type. So, when data is inserted, the DB can check if a ...


2

A very basic answer would be to let the developer know what exactly they're working with without needing to assign aliases in the event more than one table is being used. For example, if you're joining posts on comments (I'm not familiar with WordPress but just as an example), and the posts table as well as comments table have the columns title, author, ...



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