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 (2)

0
votes
1answer
305 views

Database design for surveys in MySQL

I'm trying to design an app where users take a new survey everyday (that's not the main part of app but it's an important part). I've never designed a database before so what I'm trying to do might ...
0
votes
1answer
516 views

Need help designing table with list of IDs to store

I have a table that I need to create to hold saved emails for FUTURE delivery (to, from, message, scheduled send date, etc). The catch here is that I don't know who's supposed to receive the email ...
2
votes
1answer
57 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
131 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?
-1
votes
2answers
241 views

Relationship between professor and course [closed]

I'm designing a database where I have a Course table and and Professor table. The business rules are : 1) A professor can teach many courses. (This is obvious). However, I get stuck in the second ...
2
votes
1answer
178 views

One to one mapping vs many to one mapping on data base design

I'm currently doing call detail records (CDR) data analysis as my project. On course of data-base design, I had separated the date (table contain id, day,month,year), time field (table contain id, ...
1
vote
1answer
719 views

Bus time schedule database design

Hi I'm trying to figure out how to design a database for bus time schedule. There are several lines, each line have two routes (forward and back), each route have several stations (stops) and each ...
1
vote
1answer
216 views

Database schema for a variable-level hierarchy with variable types?

A client is asking for a database structure for a medical translation app that involves narrowing down a health issue and then asking specific questions on it. The tricky part of representing this in ...
1
vote
1answer
294 views

Multiple foreign keys with shared columns for weak entities

this is my first question at stackexchange. I have a parent table (P) with two child (CC and CD): CREATE TABLE P ( A CHAR(3), B CHAR(2), CONSTRAINT PK_P PRIMARY KEY(A, B) ) CREATE TABLE CC ...
3
votes
1answer
439 views

Learning to draw entity relational diagram

I am trying to learn entity relationship diagram, following question is the objective. Information consulting firm has number of offices with employees. The employees can move offices depend on the ...
0
votes
1answer
1k views

Oracle db_name, db_unique_name, and ORACLE_HOME

I'm studying Oracle and I would like to understand the uniqueness or constrains when assigning these parameters. Suppose a machine with a single OS (host) and 2 different databases (physical). Are ...
0
votes
1answer
68 views

Modeling a database for easy counting / reporting

I have an app where user is known (user_id) and he can do several actions (action_id). Every time he makes an action I need to save the fact that he made it for reports/analytics. I guess it is ...
4
votes
2answers
3k views

How to choose a collation for international database?

I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the queries results is ordering it according to the user's language during the ...
1
vote
1answer
3k views

Get top most parent by nth child id?

Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the ...
1
vote
1answer
1k views

School database design

I am trying to design a database management system with my PHP and intermediate MySQL knowledge, keeping normalization in mind. These are the assumptions: The school has many students from grade 1 ...
2
votes
2answers
940 views

Should I use a separate table to store image file names?

I am learning about database design and I'm writing a Java GUI program with a back-end database. The main table in my database stores a different product in each row. I have some columns such as: ...
3
votes
1answer
140 views

Most efficient ordering post database design

I have posts table that is has post_order column. I store order of each post in it. when I change the order of a row from 25 to 15, I should update all the row from 15 to end. It's good for few rows, ...
1
vote
1answer
82 views

representation in ms-access

I have a database in microsoft access. I want to know how to look up a singular datum from a reference table giving a dynamic set of values. Here is a representation of what I mean: I have the ...
1
vote
3answers
206 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 ...
0
votes
2answers
1k views

Order table Database design

I'm in need of some help with the database design of order table of a shopping cart for online food ordering system. I have a design below. Tables about which I'm Concerned are order_details and ...
2
votes
0answers
169 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 ...
0
votes
0answers
348 views

Trouble converting MySql Database to SQL Server

I have an mysql database that contains 38 tables. I used "SQL Server Migration Assistant for Mysql" tool to convert this database to SQL server Database. I successfully converted 37 tables. Only one ...
3
votes
1answer
101 views

Constraint to one of the primary keys as foreign key

Table1: grid_col (col_id,f_id,f_value) (col_id,f_id) is primary key. Table2: grid (grid_id,col_id,text) (grid_id) is primary key. I want to have a constraint for grid that, col_id ...
0
votes
1answer
98 views

Database design in MySQL

I am designing a database for an web application, in this system there are multiple users. Consider, Public (Buyer). Merchant. Tele Marketing Executive(TME). Marketing Executive(ME). Lead ...
1
vote
1answer
77 views

When do you add your foreign keys?

I'm scripting out a fairly simple database so I can simply hand off a script to someone else for an application to use. I'm curious about where best practice/the community stand on this: When should ...
3
votes
2answers
185 views

Scalability: Desiging a message table

This question might be deadly simple but I wanted to make sure I am going through the correct path and not making any simple and irrevertible mistakes. My project will be based on messaging and it ...
-4
votes
2answers
157 views

Inserting images in database [closed]

How can the images be inserted in a table? Var binary is not working in structured query language command prompt,what can be the syntax?
1
vote
1answer
182 views

Storing different countries' administrative divisions

I'm designing a data base which should relate users to their administrative division for statistical purposes, considering that every country has its own type of divisions. Chile, for example, is ...
3
votes
1answer
270 views

Splitting a table based on enum value

I am re-designing our company database, and am wondering whether or not to split this table up into two (or even three) different tables based on the type field. Prospects can become clients, and in ...
2
votes
1answer
157 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 ...
5
votes
2answers
385 views

How to design index for faster deletes

I want to speed up following piece of code delete from ssn_sdo where (art_id=@art_id and skl_id=@skl_id and @level=0 ) or (art_id=@art_id and skl_id=@skl_id and @level=1 and ...
0
votes
2answers
1k views

How can I better optimize my tables and a LEFT JOIN query to find items that don't yet exist in the right table?

I have two tables that represent a list of urls and their related word indexes. Here are the table definitions for reference. desc urllist; ...
1
vote
2answers
754 views

boolean purpose column char(0) or tinyint(1)

For Boolean purpose column which one is better: nullable char(0) or tinyint(1). I know bool is an alias for tinyint(1) , but in "High Performance MySQL" book that published by O'reilly said: "If ...
2
votes
1answer
533 views

Multiple Database Design or NOT?

I have a database design issue, one that has rattled my brain. What is the project? I am designing a platform where different people register a group, each group is allowed to register their own ...
1
vote
0answers
49 views

Where should a default setting field be?

I am creating a database to track occurrences of incidents on buses based on an existing system. Currently the drivers can set a default bus number, which is used to pre-populate the bus number on the ...
1
vote
0answers
670 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 ...
1
vote
0answers
273 views

Data modelling: RBAC for small enterprise software

I'm building the core tables for a small, cloud-based enterprise app. Is this data model suitable for a role based authentication system (flat hierarchy of permissions) that allows users to work for ...
1
vote
1answer
217 views

Implementing a Record Keeper/Rollback System for a MySQL Database

What's the best way to create a record keeping table for all actions in my database? I have a number of tables inside one database and I want to create another that records all movements in every ...
3
votes
2answers
188 views

Terminology for table types

I am currently unsure of the correct terminology for types of tables that exist, or indeed whether there is a term for the examples I have. I have included my current understanding of the types that ...
7
votes
2answers
920 views

How to find parent rows that have indentical sets of child rows?

Suppose I have structure like this: Recipes Table: RecipeID Name Description RecipeIngredients Table: RecipeID IngredientID Quantity UOM What are some good ways for ...
1
vote
2answers
61 views

Can a database detect that data is out of sequence if each datapoint has a sequential ID

Can a database detect that data is out of sequence if each datapoint has a sequential ID? E.G A trading DB server receives trade ID 9, and next receives trade ID 11. In this case, I would like the DB ...
4
votes
2answers
3k views

Create table group sub-folders in Management Studio

When I was exploring the master database in SSMS I noticed that under the Tables folder there is another folder called Systems Tables that houses a bunch of tables. Is it possible for us to create a ...
1
vote
1answer
672 views

Count number of common column names among all tables in a database

Is there any way to compare every table with each other in a database, listing the number of common columns between each tables? I prefer using join over subquery and information_schema.columns over ...
1
vote
1answer
44 views

query to get list of tables that has same columns from information_schema.columns?

idea is to list all the tables that has redundant columns across database using information_schema.columns! let's say table1 = dbo.ABC columns = id, name, department, designation table2= dbo.DEF ...
2
votes
1answer
103 views

Is it ok to let users add new columns and tables to your database?

I'm basically making a database of databases. An administrative User can go in and hit "Create Database" (which is basically a table) and set up the column types and how many columns. Other users can ...
0
votes
1answer
123 views

Create primary keys based upon higher-level entity in access?

Is it possible to have access create primary keys for items by appending a string to the primary key one level above it? I am trying to create an inventory management database for my company's server ...
2
votes
3answers
238 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: ...
3
votes
2answers
87 views

Which design to choose in this case?

I am designing a database for a video game. I have some data structures like {x, y, z, dimension} used across multiple tables. For instance : Table player_death id: int player_id: int death_x: int ...
1
vote
1answer
282 views

Column of generic/variable type?

In SQL, I would like to create a table which can contain generic data types. This types can change from every row. The solution I could come up to is: CREATE TABLE TagValue ( tag VARCHAR(64), ...
0
votes
1answer
147 views

how I can create check sum to validate no changes at int string pairs on table

Business case is. 1)Users print document on paper 2)users carry printed paper to Boss, Boss sing on paper 3)after that users going to administrator to digitally sign that paper. My task is to help ...