Tag Info

Hot answers tagged

7

You could consider a cart to be a storage location. Simply add attributes to the table so that a location can be identified as type "cart" or "shelf", like so: If there's a requirement for a hierarchy - for example, if a cart might be stored in a storage location itself - you could define the hierarchy within your storagelocation table. This is not a ...


7

Based on the sample table in your link, I'd suggest it's taking issue with fields such as: phone1 phone2 or notes1 notes2 notes3 This may indicate a design flaw (but not always), and if so, one that can probably be corrected by more descriptive field names or perhaps a child table. For example, someone might have more than one phone number... what if ...


7

What you're seeing is mostly one user interface's way of displaying the structure of a PostgreSQL database. If you were using pgAdminIII, which is just another administrative interface, each database would usually show two "catalogs": information_schema and pg_catalog. It would also have a schema named "public". Database objects named "pg_*" are system ...


7

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


6

You can get rid of the anomaly by changing the FOREIGN KEY constraint (from Widgets to Carts) to include the StorageLocationID: CREATE TABLE Widgets ( widgetID NOT NULL , storageLocationID NOT NULL , cartID NULL , PRIMARY KEY (widgetID) , FOREIGN KEY (storageLocationID) REFERENCES StorageLocations ...


6

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


5

You've missed one place to get an overview of Oracle: the Concepts Guide. It covers all the major topics (including backup and recovery, which is quite important and doesn't appear in the list of links you've posted). Whats the next step? Create the Schema or Tablespace? Both! They're orthogonal. Users are logical entities that access your database. ...


5

The question I would ask is whether the direct relationship between Anthology and Composer is "important" to the system? There are all kinds of incidental relationships between tangible things that are recorded in any system. However, only certain of these are important for the purposes of the system itself. These are the ones that belong in a relational ...


4

This is a rather common problem, when the design has a "diamond" shape. With MySQL, I'd use something like this: Note that I prefer user_id as name for the primary key of users and not id for all the tables. I find the SQL code totally confusing otherwise (plus you can use the JOIN ... USING (tablename_id) syntax. The extra UNIQUE constraints in tables ...


3

Yes, there is. You have to change the database collation from CI (case insensitive) to CS (case sensitive). You can do that by using T-SQL, such as: ALTER DATABASE TEST COLLATE Latin1_General_CS_AS Or in SQL Server Management Studio: Right-click the database in Object Explorer Select Properties Select the Options tab Change a new collation in the ...


3

Your diagram has a table for Widgets with a StorageLocationID and a CartID. Not sure that makes sense logically, since the StorageLocationID refers to something physical and fixed, and the CartID can move over time. You don't seem to be tracking things over time though, so perhaps that is the disconnect. But when I think of this scenario I think that a ...


3

Just found out very neat explanation: superkey: a set of attributes which will uniquely identify each tuple in a relation candidate key: a minimal superkey primary key: a chosen candidate key secondary key: all the rest of candiate keys prime attribute: an attribute that is a part of a candidate key (key column) nonprime attribute: a nonkey column


3

Without knowing more about the business requirements, with the given structure you defined I would probably go with something like this: Expense (Id, DetId, TypeId, Amount, Date, EmployeeId) ExpenseType (Id, Name) Where Expense.TypeId is a foreign key constraint to ExpenseType.Id. ExpenseType could contain rows for daily or monthly expenses. This is ...


3

If you keep the default search_path and use the default schema public (automatically), you don't have to bother with schemas at all in your daily affairs. But if your database grows, chances are you will happily make use of them to organize objects and privileges. By setting the search_path per user / database / session / .. you can manage schemas any way ...


3

It depends on what your philosophy is around column naming. If you go for very descriptive long column names, then display_order is perfectly fine, since that is exactly what your column contains. If you are comfortable with shorter names that may seem a little cryptic, but which can be figured out pretty easily, then you could use seq or ord. For ...


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

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

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

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

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.


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


2

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


2

I would suggest doing it in the application rather than in the database with a trigger, only because it's not integral to the structure of the database; rather it's for performance reasons and I don't think doing it in the database will actually improve performance of the query that's running (it might slightly improve performance of the actual insert/update ...


2

Either "display_order" or "web_display_order", unless you have some hidden requirements. (If you support multiple applications, and each has different display requirements, [application]_display_order makes sense.) The important part can be abbreviated with "disp" and "ord" in English, as "disp_ord" or "web_disp_ord".


2

In cases where there is a legitimate option to use a substitute ingredient you can do one of several things, all involving storing the option in the ingredients array. One way could be to add a field to an array element as "allowedSubstitutions": "different liquor" another way would be to turn "ingredient" into an array which lists possible different ...


2

Also PostgreSQL, when using quoted identifiers. With your example it would look like CREATE TABLE strange ( "myfield" text, "MYFIELD" text, "MyField" text ); Have a look at the documentation, too.


2

I'd vote for #2. Will you not need a item_price and a quantity in the user_purchased table? (for which order_details looks a better name, by the way.) Or other, extra columns for the user_basket table? I can easily see how the table will get wide, with a lot of nullable columns. MySQL has no CHECK constraint so how do you plan to enforce integrity? And why ...


2

Try SchemaCrawler. It's open source and available on SourceForge. I've got some simple instructions to demonstrate it in my blog https://gilesey.wordpress.com/2012/11/15/a-lightweight-schema-diff-or-dump-from-oracle/


2

It might be helpful to review DB2 in general first. When you install DB2 it creates an instance. Think of this as a "server". An instance can have zero to many databases within it (can't say I know the limit). When you created DB2 locally it created an instance for you named DB2. Usually when you install DB2 on say a Unix server, you name your instances. ...


2

@Chris Aldrich has given a good explanation. I will just add a few things here. 1) There is no concept of a "database user" in DB2. All authentication happens outside the database or instance, in the operating system. Also, there is no direct relationship between a user ID and a schema name, unlike in Oracle. In DB2 a schema is just a logical grouping of ...



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