Database design is the process of specifying the logical and/or physical parts of a database. The goal of database design is to make a representation of some "universe of discourse" - the types of facts, business rules and other requirements that the database is intended to model.

learn more… | top users | synonyms (3)

2
votes
0answers
222 views

Geographic Hierarchies in a OLAP Cube / Data Warehouse

Are the following geographic hierarchies correct? Do any US/Canadian area codes cross state/province lines? Do any US/Canadian counties cross state/province lines? Are there countries with counties ...
2
votes
0answers
887 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 ...
2
votes
1answer
2k views

How to optimize multiple self-JOINs?

I'm looking for advice on either optimizing multiple self-joins, or a better table/DB design. One of the tables looks as follows (relevant cols only): CREATE TABLE IF NOT EXISTS CountryData ( ...
2
votes
0answers
255 views

How to execute a non-table-locking update operation on PostgreSQL? [closed]

Looking for a good way to update the value on a column for all the rows in a database (not huge, but big enough - about 10M records), without locking the whole table, so operations can continue while ...
2
votes
3answers
562 views

How can I improve my table design for different types of an entity?

Consider an accounting system as an example. I have an Entity called Client. Client can be of different types, with different fields applicable to different types. I consider creating separate tables ...
2
votes
0answers
129 views

Two different ON DELETE paths, how do I need to change the schema?

For a project in school I'm designing a website that sells items via auctions. Think eBay, but on the complexity scale of school project. We went through the process of making an ER Diagram and ...
2
votes
0answers
120 views

Digital asset management

I'm working in a company that currently develops Media Asset Management system and now we want to improve our system for document files. I have to search nearly thousand .doc and .pdf files and index ...
2
votes
2answers
219 views

What's the best practice for representing set operations in a relational database?

I have a simple database representing collections of entities and sets of those collections. Sets contain multiple collections and collections contain multiple entities. Currently, when I query all ...
2
votes
2answers
260 views

Implementation Idea for login structure (login id with two different tables)

I am creating a sample web application for a business and I am stuck at the login implementation. There is supposed to be two different users (login types?). I will have employees and customers. I ...
2
votes
0answers
2k views

Schema for an RBAC (Role-based Access Control System)

I want to implement an RBAC, Flat based. I've decided to base my system on the NIST standardization model. Although all fine and well, I have an inclusion to that design. Instead of a hierarchy ...
2
votes
2answers
520 views

Replacing composite key with surrogate

I have the following table that has a couple of million rows in it and is 99% fragmented virtually all of the time. My plan was to insert a IDENTITY field as a surrogate key to replace the current ...
2
votes
1answer
274 views

Best approach to design a database model for this situation? Clients and records

We need to store the following information for each client: Three types of record per file, for example (obviously, with real data): HDR2007022399999900000000000Soft/XX 0.0 XXX ...
2
votes
4answers
219 views

MySql Database solution for specific web app problem

Well, I need to create a comment system for posts that people add to my web app. This web app is just like facebook or myspace posts. Users add posts and people can comment on them. But the problem is ...
2
votes
0answers
301 views

How to seperate sensitive data in database(MySql) [closed]

I need to design a database that will contains information about personal disease of users. What can be the approach in order to implement the columns of the DB's tables: encrypt the information, ...
2
votes
0answers
540 views

How do I auto-fill data in Microsoft Access 2003?

I am creating a Purchase Order Form at work using Microsoft Access 2003 so that it can be saved and archived in a database and reduce paper costs. These are my current dilemmas: I am unable to ...
1
vote
3answers
142 views

Remove duplicate values from query result

I am using Postgres 9.3.9. I have 2 tables : f_agent : CREATE TABLE f_agent ( f_agent__id bigserial NOT NULL, f_agent__total_users integer NOT NULL, f_agent__dim_time_id integer NOT NULL, ...
1
vote
3answers
399 views

Which one is better performance and (approach): store as rows or as a varchar column

OptionA: TableA: TableA_ID (PK,int) .. TableB: TableB_ID (PK,int) .. TableC: TableC_ID (PK,int) .. TableD: TableA_ID (FK, int) TableB_ID (FK, int) TableC_ID (FK, int) (Nullable) ...
1
vote
4answers
372 views

Database/Storage engine suggestion for a project involving heavy inserts?

I am looking into a project which involves large number of inserts daily.I will have a list of user's (for example a user set of 500k ) , for which I need to monitor daily certain activities ...
1
vote
3answers
349 views

Database throughful design vs Quick and dirty design comparison

Take the representation of an Address, here below is a complete and very detailed implementation: here instead is a quick implementation (which more or less contains the same fields, imagine all ...
1
vote
3answers
175 views

How to handle update or insert situations

From time to time my stored procedures looks like create procedure handle_data @fk int ,@value varchar(10) as begin if exists (select * from my_table ...
1
vote
1answer
1k views

Many To Many Relationships

I am working on a conceptual model for a database. I have the following entities: Student (id, name, date of birth, department) Subject (id, name) This is a many-to-many relationship, one student ...
1
vote
3answers
94 views

Need suggestion for placing foreign keys in the databases

I have the database for customers. The customers table is something like this +------------------+ | Customers | +------------------+ | id (PK) | | business_email | | business_name ...
1
vote
2answers
299 views

What is wrong in debunking myths about clustered indexes?

What is wrong in code. or its results, illustrating that clustered index is evil [1]? and how to de-debunk, i.e. to return to customary myths and best practices? [1] Debunking myths about ...
1
vote
2answers
158 views

How to architect an analytical BI platform? [closed]

I am working on a new BI project for a medium sized corporation. Right now there is no Analytical infrastructure in place and the reporting is done manually in Excel. There are a few different data ...
1
vote
1answer
586 views

Record with variable number of columns?

I am trying to model a cable mapping for devices. Each device has multiple cards, and each of those cards has multiple ports. Since both the number of cards and ports vary, I am confused on how to ...
1
vote
2answers
393 views

To normalize or not to normalize for few distinct values

Assuming in a Postgres database, you have a table called party, which can have less than 5 well-defined party_types such as 'Person' or 'Organization'. Would you store the party_type in the party ...
1
vote
3answers
297 views

Is this good/bad database design and why?

I am building a database for a small project. I don't have much experience so I am unsure, but I get the feeling I am not approaching this correctly. I have a system which allows users to create ...
1
vote
4answers
948 views

Unsure how to implement certain constraints and relations in this relational database schema

I am in the middle of designing the dB for school project- airline ticketing database.(Using MySQL workbench) So far, I came up with the following design: Here are couple things I can't figure out: ...
1
vote
2answers
520 views

Database Design - One table from many source(One to One)

I have a 'Text' table, the text table may came from different source, like Tweet, SMS, Email, etc. and each source have their own log, and Text is came from the source. so i came with some solution, ...
1
vote
3answers
5k views

Many-to-one Subselection in single query

I have two tables with a foreign key from T1->T2, in a one-to-many relationship. That is, 1 tuple in table T1 is associated with 0..N tuples in T2. To create a simple example, lets say T1 is Cars, ...
1
vote
1answer
252 views

Do you feel like DBA/DBD Is a “secure Field” still? [closed]

I noticed recently alot of Database jobs/Development have kind been pushed onto general developers, not to mention with NoSQL Solutions (Which IMO are probably a little easier for Non-DBA's to throw ...
1
vote
2answers
429 views

Messaging Database - How to handle multiple recipients

So I am trying to develop a relational database (I'm using MySQL, planning on a Java front end) where I have 3 tables (People, where I store names and relevant personal information... and Messages ...
1
vote
1answer
2k views

Postgresql vs MySQL - Which is better for join queries & writing data(inserts) [closed]

I have to design a database which will end up with 50M records in a single table(there will be other tables with lesser number of records). I'm more concerned with join queries & writing ...
1
vote
1answer
233 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 ...
1
vote
2answers
202 views

Suggestion for storing a lot of simple data

I'm looking to crawl a lot of webpages (500,000,000,000) records and be able to store the linking structure for a later date. The way I planned on laying out the database was as follows: 2 Tables ...
1
vote
3answers
6k views

Designing a Products database: One Products table or many separated by vendor?

In order to prevent the unwieldiness of managing product data spread across a number of excel spreadsheets, I have set up a Products database. My data is in spreadsheets because it was (somewhat) ...
1
vote
3answers
459 views

Mysql error while creating FK

here is structure of both tables category table CREATE TABLE category ( `catID` bigint(20) NOT NULL AUTO_INCREMENT, `title` varchar(60) NOT NULL, `created_at` timestamp NOT NULL DEFAULT ...
1
vote
2answers
528 views

Reporting Database Considerations

We have a very large database that we want to write almost real time. We want to take data and move it to another instance of the database on top of which will be a GUI and MI tool which will be able ...
1
vote
2answers
635 views

Designing a database tables for the following scenario

I just got following entities Employee, Client and Company. Where Employee and Client have one to many relationship with Company ie A single employee may be mapped to attend to many companies and ...
1
vote
4answers
188 views

T-SQL insert value based on identity column

Is it possible to have a table with PK based on Identity columns? Lets say I have a table of customers, two fields are important for our case Id which is PK nvarchar(20) and SequenceId which is int ...
1
vote
1answer
61 views

Does a Key-Value type table still needs a auto increment key?

Suppose that I have a very simple table to store variable-like data, like this: +-----------+-----------------+ | key | value | +-----------+-----------------+ | site_url | ...
1
vote
2answers
68 views

Why WordPress use a column prefix? [closed]

I'm trying to learn some basic database design through open source projects. And look into the WordPress database schema, the posts table for example, you'll find something like this post_author ...
1
vote
1answer
257 views

Precautions when designing database schema if tables are going to have billons of rows

I am about to start work on telephone dictionary kind of project. Its confirm that there are going to be billions of records in dictionary table, and each entry from this dictionary table may have ...
1
vote
1answer
588 views

Modeling staff dimension in data warehouse

I need to write a data warehouse diagram. I've got a problem with staff area. I have to store a information about workers details like name, age etc., and workers job time and agreement details like ...
1
vote
5answers
10k views

Coding an accounting database from scratch? [closed]

My new medical practice is picking up fast and my homemade Libreoffice Calc spreadsheet is quickly becoming cumbersome to track patient visits, accounts receivable, overhead reconciliation, etc. Most ...
1
vote
1answer
1k views

How to handle huge table

We have a problem with a few tables that are very very big slowing down the system. so my question is if that size is normal for the amount of rows in the table, and also what could be done to make it ...
1
vote
1answer
308 views

One address table for many types of addresses

I have an application where I have to store adresses of multiple types of location. For example, my app requires addresses of people, hotels and event venues. Should I create separate tables for ...
1
vote
1answer
212 views

Choosing the right field data type in SQL Server 2008

I got a request to create a new table that will store certain information for customers. The definition of the table is somewhat irrelevant except for one field. This field will store ANSI characters ...
1
vote
3answers
4k views

how to Export a document contain tables name, columns names and column description from SQL Server 2008?

I created a db in sql server 2008 and I wrote for some columns description to describe them, What I want now is to find a way to export these tables design including the description for each column ...
1
vote
2answers
331 views

Best type to use in a composite key when one of the values varies in type

I am building a data store for the content of multiple blog sites which have been scraped. Each of these sites is going to have an entry in a Blog table BlogId Url ...