A schema of a database system is its structure described in a formal language supported by the database management system (DBMS) and refers to the organization of data to create a blueprint of how a database will be constructed (divided into database tables).
2
votes
2answers
76 views
SQL Server 2008 R2 : A Possible indexing strategy for the given schema?
We have these four tables:
Store (
row bigint,
id uniqueidentifier,
name varchar
)
Products (
row bigint,
id uniqueidentifier,
storeID uniqueidentifier,
productname ...
1
vote
0answers
83 views
Are duplicates ever acceptable in a database
A warehouse system I am working on allows inwards consignments [type = 0] and outwards consignments [type = 1]. The user selects multiple products for each consignment and then multiple warehouse ...
1
vote
1answer
46 views
Schema Change/Update script for Database deploy [closed]
I have a need to change the database schema . I'm planning to write Schema change and update scripts for tracking database changes and updating them. I followed
Versioning Databases – Change Scripts
...
0
votes
1answer
47 views
Whether or not to create separate tables
I am working on a project, where I need to store the information related to A and B, the A and B both have identical columns.There might be thousands of As and Bs. Should I use "type" column to ...
0
votes
1answer
43 views
is there a recommended way to update many postgres schemes
I have a database in postgres which has 15 schemes, the schemes are identical with the same tables, views and functions.
the system uses one scheme at a time, as if they were separate databases. So ...
0
votes
1answer
56 views
How does security context propagate between schemas within a database?
We have a SQL Server 2008 database that contains two schema's. The owners of these schema's have been granted full SELECT permissions to all tables within each others ownership.
I have created a ...
1
vote
2answers
69 views
What is a standard or conventional name for a column representing display order? [closed]
For example, a junction table associating a product and its pictures:
create table product_picture (
product_id bigint not null references product(id),
picture_id bigint not null references ...
0
votes
0answers
23 views
please help me connect workbench on 5 system having one server
We have computing software which has one server for 5 computers.
I can work on server in MySQL workbench but when i try to work on second computer i get error failed to create ipc port : access is ...
2
votes
1answer
29 views
How to handle “or” possibilities in MongoDB
I'm new to MongoDB and by no means an expert in DB design in general. This feels like a really basic question that's probably been answered a million times, but I'm having a surprisingly hard time ...
-4
votes
4answers
114 views
Is there a DB that will allow the same field name with different cases?
Is there a DB that will allow a user to create a table with two fields, one calls MYFIELD, the other called myfield?
0
votes
1answer
20 views
Why can't the schema owner create a new table?
I have a DB user called STANMAN which owns the STANMAN schema. When I try to create a table as STANMAN, it says STANMAN has insufficient privileges. How could a schema-owner not already have ...
3
votes
4answers
113 views
Open source tools for Oracle schema compares
I'm looking for an open source tool to generate DDL scripts. Many tools call these synchronization scripts. We essentially want to compare schema of different environments (ie: DEV to QA, QA to ...
2
votes
1answer
53 views
MySQL Performance Gains By Splitting Tables?
I am designing a database schema to be used with MySQL. The database needs to keep track of a users basket items and purchased items. I've thought about two ways I could do this but not sure which ...
1
vote
0answers
37 views
which role-permission database design?
I want to manage roles and permissions. Most of designs on web look like this
tables:
Users
Roles
UserRoles
Permissions
RolePermissions
Here, what is permissions? I am thinking for such a design ...
0
votes
1answer
82 views
What's a good way to model user authorization to hierarchical data?
I'm wanting to implement something that's basically an ACL in SQL Server (2008, we'll be moving to 2012). We're working with farm production information. We will have users who need to be authorized ...
1
vote
1answer
81 views
Creating schema for a shop that sells items from multiple tables
I am trying to create a table in mySQl that contains a list of all the items that a shops. For example:
shops (shop_ID (PK), shop_name)
shops_and_items (shop_ID (PK), item_ID (PK), price)
The issue ...
2
votes
3answers
99 views
Database design for conditional detail table
I have an expense management system for monthly expenses. Expenses are of two types: one-time monthly expenses, and daily expenses for each day of month. I have created the following tables:
...
1
vote
1answer
89 views
Oracle schema migration to new database with zero downtime
I have a problem:
I have two production schemas on one database each serving two different appplicaitons.
I need to migrate (move) one schema to an entirely new database that will be dedicated for ...
2
votes
1answer
43 views
Column type unknown at creation time
I have a table in a MySQL database that stores survey results.
CREATE TABLE survey_responses (
response_id INTEGER UNSIGNED AUTO_INCREMENT,
survey_id INTEGER UNSIGNED NOT NULL,
question_id ...
1
vote
1answer
304 views
How can extract the table schema from just the .frm file?
I have extracted the data directory of mysql from a backup and need to get the schema from an old table, however it's a backup from a different machine.
I have read a fair number of tutorials today ...
2
votes
1answer
38 views
Strategies for summary data in parent record based on detail data?
I have parent-child relationship as shown below. I need to find the first order date for each customer. Obviously, writing a query to do this is fairly straight-forward but it does require reading all ...
6
votes
4answers
416 views
How can a database be normalized when an optional field causes denormalization?
Suppose you have a warehouse full of widgets. Each widget in the warehouse is stored in a specific identifiable location within the warehouse. You might have a schema which looks like this:
A ...
0
votes
1answer
90 views
Candidate key = Key = Minimal Superkey?
I got a little confused by all these keys. As I understand
A key of a relational schema =
The minimal subset of a superkey that is still a key =
A candidate key
Is this correct or not?
0
votes
1answer
54 views
When is it worth it to use Triggers and stored procedures/functions? [closed]
As an example, lets say I have a forum application and for every topic that is created, I have a trigger that fires a database function creating a post.(lets assume we have a topics table and a post ...
1
vote
1answer
65 views
Mysql: Schema/Query Performance Approach for aggregated mailbox folders
I am about to code a messaging system where users can write messages to other users. User can create custom inbox folders for sorting the messages they receive, however, every user has 2 main inboxes: ...
1
vote
1answer
77 views
What is the meaning of this anomaly warning message?
I have run an analysis with SchemaSpy.
The tool gave me a warning message for two tables. The warning message is:
Tables with incrementing column names, potentially indicating denormalization
...
0
votes
0answers
431 views
How to create table set from several XSD in SQL Server 2012 or with VS 2012
Is there any solution in T-SQL or in C# how to create XML schema collection in T-SQL or C#?
Problem is I cannot use:
USE Database_test
GO
CREATE XML SCHEMA COLLECTION Collection_from_XSD AS
N'
...
4
votes
1answer
76 views
Many:Many with Shared Relation
I'm modelling data with multiplicity like this:
Each Composition/Anthology related pair must share a Composer. Also, each Anthology must contain at least one Composition. How would you recommend I ...
0
votes
1answer
74 views
Postgres - Schema information within Trigger?
Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x).
Reviewing patterns for the following problem: I ...
1
vote
1answer
396 views
Oracle database created whats next? Create Schema or Create Tablespace?
I'm a programmer that's being thrown to the DBA/Sysadmin island all by myself with a volleyball that I named Wilson. I'm trying to survive here.
I'm supposed to create a database for an application. ...
0
votes
0answers
72 views
XML Data convert to SQL database,
I have an XML feed that i need to parse daily, i want to store all of the data from the XML feed in a SQL table so then that data can be queried and manipulated by my clients.
I can currently parse ...
2
votes
2answers
88 views
PostgreSQL and default Schemas
Whenever I create a brand new database in PostgreSQL Maestro it creates the following list of default schemas:
Now from my understanding Schemas are like folders for organization, etc. So the ...
0
votes
0answers
41 views
In regards to GS1 barcode - what size DB field are used?
In regards to GS1 barcode - what size DB field is used or is the entire GS1 typically not stored in database, but rather processed in the application layer and stored in the database as a collection ...
0
votes
1answer
111 views
Database schema design help needed
I am developing a PHP application expecting millions of records both parent and children. My goal is to design an optimized database design to achieve high speed and performance.
This application ...
1
vote
2answers
1k views
How do I list all schemas in PostgreSQL?
When using PostgreSQL v9.1, how do I list all of the schemas using SQL?
I was expecting something along the lines of:
SELECT something FROM pg_blah;
0
votes
1answer
244 views
Oracle schema import is not importing all the tables present in the schema dump file
I have exported an existing oracle schema from another machine and then imported it in my local machine. Import was successful, but some tables which are present in the export dump file are not ...
0
votes
1answer
27 views
Modify legacy table schemas that have no primary key
I've been tasked with updating our database (SQL Server 2008) so that every table in it has a proper primary key defined. Due to various reasons over the years we have somehow built up some legacy ...
1
vote
0answers
178 views
How to represent class table inheritance (current DBMS-specific way please)?
I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server.
I found a recommendation for MySQL here (How do I map an IS-A relationship into a ...
1
vote
1answer
170 views
Compare two MySQL dump files
I am trying to compare two MySQL dump files. One was made from a fresh install of a web application and the other is a dump from the same web application that has been upgraded from a previous version ...
2
votes
1answer
188 views
Will table inheritance in PostgreSql suit this situation?
Let's start off by saying, I'm new to PSQL and coming from a MSSQL background. I'm starting to design a DB in PSQL.
So my main aim is to have a Main schema in this DB, which I am going to use to ...
6
votes
3answers
3k views
Difference between database vs user vs schema
I'm really confused with the terms database, user and schema. Can anyone explain how they are different from each other (if they are)?
If they are same then, what are the similarities between them? ...
1
vote
1answer
38 views
Multiligual database design
I assume that the best approach for many languages (5 and more) is concept based on:
CREATE TABLE `language` (
`language_id` char(2) NOT NULL,
`collation` varchar(64) NOT NULL,
...
1
vote
2answers
89 views
Extensible Asset Database Schema
Background
I work for a decent sized company in the hardware sector. Currently, we have a number of varying assets that need to be tracked. Until recently, this was done by hand as was an ...
0
votes
1answer
333 views
Granting permissions only on a set list of objects
I have a SQL Server 2005 database with a large number of tables in the dbo schema. I now created a new schema (call it myschema) that only has three table-valued functions and two stored procedures in ...
2
votes
2answers
49 views
Best practice for database where the values can't change
Suppose I have a table Inspection with a field for Place (the location where the Inspection occurred). The field is a foreign key to a Place table, where each place has a Name, among other things. The ...
0
votes
2answers
544 views
MS Access ER diagram
I have database scheme for Microsoft Access. I'm interested how I can convert the scheme into an ER diagram? Is there any tool that can do this based on scheme relations?
6
votes
4answers
222 views
Database Design - different objects with shared tagging
My background is more in web programming rather than database administration, so please correct me if I'm using the wrong terminology here. I'm trying to figure out the best way to design the ...
1
vote
1answer
554 views
Difference between Owned Schemas and Role Membership for Microsoft SQL Server
Are there differences between Owned Schemas and Role Members?
From my understanding, the Role Members are compulsory - user need to have at least one of the check box under Role Members checked.
...
1
vote
2answers
527 views
SQL Server - backup / restore schema only (not a duplicate if that's what you're thinking!)
We have a DB on over 100 servers of which we want to backup / restore the structure of just 1 at a time to a specific UAT database. They're all called the same thing but vary in terms of schema - ...
2
votes
1answer
99 views
Tools and methodologies to keep to DBs aligned [closed]
2 DBs having schemas that represent the same semantic objects.
The first one is production DB (Non-RDBMS, in-house implemented in-memory DB with shitload of RAM). Other is Postgres.
Once in a while ...
