The logical model and/or physical design of a database.

learn more… | top users | synonyms (4)

7
votes
1answer
897 views

LDF and MDF file sizes for a dynamically created Database

I am creating a dynamic database from C# code, and would like the database to have a fixed size (for example 5 GB). While creating the DB through code, I need to specify MDF and LDF max file sizes. ...
7
votes
1answer
101 views

Functional dependencies applies to whole database or to a particular relation?

I am new to dbms. I saw a lecture video about functional dependencies. But I am confused a bit that whether functional dependency is a constraint put on whole database or on a particular schema?
7
votes
2answers
2k views

nvarchar(max) converting to varchar and table optimization

I am working with a table that has all character types set to nvarchar some of them are nvarchar(max). We are converting all these to varchar and specifying a character width based upon the actual ...
7
votes
3answers
3k views

Separate archive tables or soft delete for inventory database

I'm building an inventory database that tracks computer equipment and other hardware devices. At some point in any device's life it is retired and gets archived. After it becomes archived it needs to ...
7
votes
2answers
5k views

Bit vs. Boolean columns

Given that bit fields are just binary representations of data and need to be queried in slightly "strange" way. Does it actually provide any benefit using a bit field for a boolean value? From what I ...
7
votes
1answer
436 views

Efficiently storing sets of key-value pairs with wildly different keys

I inherited an application that associates many different types of activities with a site. There are roughly 100 different activity types, and each one has differing set of 3-10 fields. However, all ...
7
votes
3answers
2k views

Appropriate multi-language schema, or overkill?

UPDATE 2: I've actually ended up using this, and it's great after a couple tweaks. Here's my post on its actual design, and in action: http://tim.hithlonde.com/2013/lemon-schema-works/ I am building ...
7
votes
1answer
98 views

sysschedules datatype choices

I am curious, why does SQL Server save the values in msdb.dbo.sysschedules for date and times as int instead of datetime. I assume the reason dates back to something in SQL Server 2000. Was it a ...
7
votes
3answers
6k views

Best practices for history/temporal tables?

Suppose I have an object, with certain fields which I want to track history, and certain fields which I do not want to track history. From a normalization perspective, is the following schema alright: ...
7
votes
3answers
197 views

Choice- or ids-list- field representation

I wonder what is the best way to represent a user setting that could either take a value from a set (such as 'High', 'Medium' or 'Low') or be a list of IDs from another (or the same!) table (Many to ...
7
votes
1answer
257 views

How can I make my database more concise?

I am writing an application for Android using SQLite (backed by ORMlite if it matters). So far I'm in the conceptual stage and working on the database design. The database is being designed to contain ...
7
votes
1answer
588 views

How to store and query products the price of which depends on a complex set of conditions?

I haven't decided on a database. I have expirience with MySQl and I'm intrigued by MongoDB. Let the products be packages of cable tv providers: { name: "Virgin TV", price: 20 }, { name: ...
7
votes
2answers
10k views

SQL Server: query performance (search 2 million rows)

Got an interesting one for all of you SQL guru's out there. Now this search is only taking a couple of seconds, but it's quite intensive and there must be a better way. Maybe I'm expecting too much? ...
7
votes
1answer
330 views

Could Stack Overflow run on SQL Server Web Edition?

I am starting a new web venture that may need to scale to a high number of users. I am confident with the SPLA licencing for SQL Server Web edition, but want to know if I will need to factor in ...
7
votes
1answer
2k views

Full text search through many tables in MySQL

We have high traffic NEWS websites, I want to add a feature that every user can search through over all content of site, such as news, polls, comments, galleries,etc . Each of contents type has its ...
7
votes
2answers
679 views

Best relational database structure for this data

I'm in the process of creating a database scheme for the following scenario: There are users Users have roles (such as "Developer" or "CEO") Roles have applications (such as "Topdesk") Applications ...
7
votes
1answer
194 views

Data Warehouse: How can i query daily snapshots?

I have some snapshots of a database that are not timeseries. For example: Snapshot day 1: +----+---------------+------------+------------+ | ID | Title | Category | Date | ...
7
votes
1answer
502 views

CouchDB and document versioning

I'm currently working on a wiki-esque application using CouchDB and am trying to implement a document versioning scheme. The way I see it there are two ways of doing this: Store each version as a ...
6
votes
2answers
15k views

Efficient way of changing VARCHAR to NVARCHAR fields in a large table in SQL Server 2008?

I am aware of when adding new fields to large tables, it is recommended to add them to the end of the fields rather than somewhere in the middle, and wondering if something like this applies when ...
6
votes
4answers
536 views

Purpose of IDs in a User Table Database

In a table, such as a user table, where there will only be 1 row with a for any particular user, why have an ID field, let alone as the primary key?
6
votes
2answers
415 views

What is the name of this schema pattern

Its been a few years... but I've run into a problem-set that reminds me of a schema design pattern and I'm trying to recall the details. Essentially, the pattern consists of one database which is 3 ...
6
votes
5answers
5k views

Sharing a single primary key sequence across a database?

Is it an acceptable practice to use a single sequence as a primary key across all tables (instead of a primary key being unique for a given table, it is unique for all tables)? If so, is it ...
6
votes
4answers
829 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 ...
6
votes
6answers
154 views

How to do a bulk load of a database when application are relying on it?

In a daily routine, I need to drop and do a bulk load of a database. The problem is that I have a WebApp relying on that database. So I can't drop the database just like this. What would be a good ...
6
votes
3answers
3k views

SQL Server Primary key / clustered index design decision

Looking for some advice regarding a table / index design decision I've got to make on some tables that I've got to port into SQL server from an existing 4GL based database. I've got a product history ...
6
votes
3answers
2k views

PK as ROWGUIDCOL or use a separate rowguid column?

There's a long-winded debate going on here so I'd like to hear other opinions. I have many tables with uniqueidentifier clustered PK. Whether this is a good idea is out of scope here (and it's not ...
6
votes
2answers
3k views

Why use separate databases for high traffic/volume tables?

While looking at the database structure of an application I am using I recognized that it uses 3 different databases on the same SQL Server instance for different things. The first one contains the ...
6
votes
3answers
774 views

Help with a scenario of music groups and members in ERD design

Can someone please show me how to construct this ER diagram with the following scenario? I’m confused with the ‘or’ part of it. Description An Artist has a Name, and must be either a Group or a ...
6
votes
2answers
1k views

Increase all primary key values by 1

How do I increase all of my EmployeeID values by one if it is the primary key?
6
votes
3answers
16k views

How to model inheritance of two tables mysql

I have some tables where I store data and depending on the type of person (worker,civil) that did a job I want to store it a table 'event', now these guys rescue an animal (thereis a table animal). ...
6
votes
4answers
2k views

What exactly is overlapping candidate key?

Can some one please explain to me in simple terms what is overlapping candidate key ? What is overlapping as the name suggests? Consider the below relation R(L,M,N,O,P) { M -> O NO -> ...
6
votes
2answers
2k views

Best way to handle dates prior to 1000 A.D. in MySQL?

I am creating a database for records that extend prior to 1000 AD, but MySQL Date and DateTime fields only support dates starting at 1000. Is there a way that would be more convenient than either ...
6
votes
1answer
3k views

What's difference between standard view and indexed view in SQL Server [closed]

I learn that the indexed view holds the data from base tables while standard view does not holds data.Is it means that the indexed view need memory and standard view don't? Indexed view needs to be ...
6
votes
4answers
237 views

Dynamic contacts database

I'm trying to create a database for contacts. I must have many categories for every contact, like Home, Office, Emails, Phones. In every category one can add more than one value. Also the categories ...
6
votes
1answer
699 views

Convert clustered primary key to non-clustered and use another column for the clustered index

I have a large table with a primary key (GUID) that is also the clustered index. There is already a integer sequence based field. So I want to leave the GUID as the PK and make the integer column the ...
6
votes
2answers
4k views

Index max row size error

Is there a upper bound for an array column? I am getting this error when inserting into the array field - PG::Error: ERROR: index row size 3480 exceeds maximum 2712 for index "ix_data" Here's my ...
6
votes
2answers
662 views

Table contains multiple names for same company

I have a table of monetary transactions that has a field for who the money was paid to or received from. Transactions are recorded using info from paper invoices, paper reciepts, internet reciepts, ...
6
votes
2answers
215 views

Efficient Database schema for multi-lingual content?

Assume I have a table for blog posts, posts, and schema as id author title content and each post can belonged to some categories id name descrtiption So a blog post can belong to multiple ...
6
votes
3answers
397 views

Need help on my first ER diagram

I just started my first Database course online, and I have a homework assignment to create an E-R diagram from a list of specifications. The specs are as follows: The company is organized into ...
6
votes
2answers
518 views

Should I add a column or 1-1 table if property is usualy null

There's a new unique property we need to add, which is relevant only to a specific subset (less than 1%). We thought it's better to add a new table which will be 1-1, than to add a new column which ...
6
votes
1answer
5k views

2NF decomposition + database normalization

If anyone knows about 2NF I'd greatly appreciate if you could tell me if my understanding of it correct my book does not even mention it (except to say is of "historical significance") and I haven't ...
6
votes
1answer
979 views

Are there any performance benefits to using a hash table with no clustered index?

I have this table: CREATE TABLE [dbo].[relatea] ( [mid] [varchar](16) NOT NULL, [sid] [varchar](16) NOT NULL ) It stores hash matches. Is there any benefit to having this as a heap? It has ...
6
votes
2answers
324 views

Design: How do you decide when items are related vs when they are attributes?

I've been trying to figure this out for a while. Let's say you have a person, for example. An attribute of a person is his or her social security number, right? But a person also has a social ...
6
votes
2answers
2k views

Recommended model for application level event logging

I am going to be storing application level events (user A added thing, user B updated that etc) for both public and private consumption. Below is an oversimplified model of the tables and their ...
6
votes
1answer
791 views

What makes an OLTP database an OLTP database?

This is a pretty broad question. I know the definition of OLTP. But it seems like unless you have an environment that has a clear distinction between OLTP and OLAP, in the sense of a database that ...
6
votes
2answers
2k views

Unknown Db file format - best way to find out?

A client has given me the data he wants visualised in my project - but I don't know what DB format it's in. He's pretty unclear, also.. I have access to the table structures, but don't know how to ...
6
votes
2answers
248 views

Table Relationship - One to Few (Many)

I know the title may seem confusing but I have yet to find a solid answer to this design pattern. Let's say I want to create an Employee table and have multiple addresses for each employee. So this ...
6
votes
1answer
1k views

Better to use a RANGE RIGHT or RANGE LEFT Partition Scheme

I'd like to know if it is better to use a RANGE RIGHT partition scheme or a RANGE LEFT partition scheme? Is one of these options by default a better choice, or is it heavily dependent on the actual ...
6
votes
2answers
499 views

Using 100's of schemas in a database SQL2008R2?

We are in SSAS type of environment with 100's of clients. Currently each client gets a database and we have 200 per instance on SQL Server 2008R2. This presents limitations with snapmanager being ...
6
votes
2answers
4k views

Which datatype to store XML data in: VARCHAR(MAX) or XML

I am defining a schema for a new set of resources using SQL Server 2008... In this case, each record (e.g. row) will need to store XML fragments. From time-to-time; although not frequently; I'll need ...