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)

1
vote
2answers
88 views

Table Design - Identical tables for different statuses

I am developing a transactional System and the below is a simplified scenario of my problem. Users enter JOBS in a system. A User first enters a JOBS with a DRAFT status (and the Job is given a ...
1
vote
1answer
74 views

Tables of a huge database

When I work on a huge database, and after following normalization rules, I found that I have many tables for each part of my huge database. That makes me worry about the final number of tables, it ...
1
vote
3answers
70 views

Decorating an existing database table with information that no one else cares about

We have a table that many processes read from. Some of them want to keep track of the fact they have processed this data, whereas others simply rely on fetching the data from the table based on user ...
1
vote
1answer
122 views

Where do caching servers conceptually fit in my architecture?

Given the simple well-known architecture below: Where (conceptually) does the caching server(s) sit?
1
vote
1answer
136 views

Splitting a table based on business logic

This is mostly a design question and I'm looking for any feedback about the merits or flaws of this approach. Typically, I break database design into two categories, Business Logic and Recorded Data. ...
1
vote
1answer
192 views

Guidance on simplifying the following diagram

I have a case scenario where a customer can book accommodation or flight only or both. I have modeled the accommodation part, however i am making things complex by over-thinking how stuff works in ...
1
vote
1answer
418 views

Does composite cluster index affect performance of seek/scan non-cluster index which is part of the composite?

I have bank transaction table with columns Division Brcode TrDate ProjectCode Accountnumber EffectiveDate DrCr Amount EndBalance In the other table the Brcode itself is unique and is assigned under ...
1
vote
2answers
337 views

Determining sizes for Separated Partitions of programs, data and logs in SQL Server

As per the recommendations of the CIS benchmarks for SQL Server, I want to create separate partitions for the data, the programs and the logs on my SQL Server dedicated server. Question is, what is ...
1
vote
1answer
154 views

In a recruiting website, should I force a choice between one table for jobseekers and employers or 2 separate tables?

I'm working on a recruiting web site and I was wondering what would be best between: 1) A User table, the employer accounts are linked to a company (therefore when companyID is null then it's a job ...
1
vote
1answer
16 views

Duplicate Data from two column

i have a table which has only two column one is pid(Photo ID) and another one is tid(Tag ID) +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
2answers
93 views

Confusion with Foreign Keys

I've been tasked to build a somewhat basic Prisoner database and I've run into a situation where I don't know what the appropriate route to take is. I have a Prisoner table, that looks like the ...
1
vote
1answer
53 views

Best approach on storing/displaying sanitized data

Surfing the web I find two approaches on storing data in databases. Approach 1: Store data in the database with the html-specialcharacters sanitized $userdata = $_POST['field']; ...
1
vote
1answer
5 views

Source control makes sure my code is never lost, how do I make sure my users/orders database is never lost?

Everyone is recommending the use of git, automated tests, continuous integration, automated deployment and so on, to reduce human error during all stages of web development. I have yet to come across ...
1
vote
1answer
69 views

Autoclose property resets to true after attaching db even if model is set to false

I am using SQL Server Express 2008 R2. The auto close property of a newly created database will be set to value same as model db. In my case I have set autoclose to false for model so that when I ...
1
vote
1answer
61 views

Better way to manage data integrity, without having too many composite FK's

I have following tables: class_table (class_id = Auto Incremented Primary Key (AIPK) of this table) user_table (class_id, user_id (AIPK of this table)) course_table(class_id, course_id (AIPK of this ...
1
vote
1answer
47 views

Is it more efficient to store 0 and 1 for a binary value or just empty and 1 in MySQL

Is it more efficient to store 0 and 1 for a binary value indicating whether a flag is up or down or just empty and then set 1 for rows where the flag is up. Using MySQL. E.g. Flag 0 1 1 0 0 Or ...
1
vote
1answer
55 views

Is deriving a value using multiple joins a good idea? [closed]

I am working on a university project. I need to create a database from conceptual to implementation. That allows customers to create an order with multiple products the specification says that we ...
1
vote
3answers
182 views

GUID stored in a varchar field

I have inherited several databases that use GUIDs as PKs. Instead of all the datatypes being uniqueidentifier most are varchar(50) and some varchar(100). Fields are true GUID some where created by ...
1
vote
1answer
200 views

Design DB for users with different information fields?

Let's say I want to register all faculty of a university and they are in different fields and have different resume information. Here we have some common fields and some field specific fields. for ...
1
vote
1answer
116 views

How to link a customer to a country when the city/state isn't known

We have a situation where medical systems are sold to hospital groups, but at the time of the order, we don't always know into which state and city the system will be installed. We always know the ...
1
vote
1answer
409 views

Need advice for table design for multi-user access

I have an inventory application that needs to support multi-user. As of right now, only one user can access and manage their items. I've gotten a lot of requests to support multiple users so they can ...
1
vote
2answers
355 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 ...
1
vote
2answers
55 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 ...
1
vote
2answers
243 views

How should I store multiple relationships (many posts to many categories)?

I have posts stored in a posts table and I want each post to have multiple categories, what is the most efficient way to store the categories and their relationships ? I thought of it this way first: ...
1
vote
1answer
89 views

Approach for a 2,000 table migration

What might be the best approach when converting or migrating a database containing over 2,000 tables? Where would you begin to tackle such an issue? Steps to take in the first weeks of designing? ...
1
vote
1answer
51 views

Adding users to a list and sort by category?

I'm using SQL-Server 2012 and I would like to know how to solve this program. So: I want to create a page that allows admins to manage a project, and part of it, would be to add members/employees to ...
1
vote
2answers
112 views

Handling expiration in a course registration or ticketing system

I want to restructure the database for a web application I inherited for handling summer camp registrations, but my question is relevant to any event registration or ticketing system. My question is ...
1
vote
2answers
276 views

Refactoring/normalization - but almost empty table

I normalized a legacy DB into this structure: But I'm not sure if it is correctly normalized. I don't feel very comfortable with the almost empty filters table. The requirements A group contains ...
1
vote
2answers
194 views

Database Design question, calculate or save financial positions

As the question states i'm currently internally strugling witha database design question. I work as a software developer (and i tend to do everything database related aswell since we're rather small) ...
1
vote
2answers
2k views

Multiple parents and multiple children in product categories

I am making a ecommerce site. In this site I want to categorise the items into three different layers primary category sub category sub category >>electronic ...
1
vote
1answer
134 views

Multiple primary keys

I am new to database design and have been looking at some example data models here. One thing I don't understand is if you have a table like client_addresses from the link above: ...
1
vote
2answers
184 views

XML Large DataBase is the best? [closed]

Is the best way to implement a large database is XML? Or is there a better way? 1 - reduce the time complexity 2 - Reduce the amount of traffic between the tables And other factors ... Which ...
1
vote
1answer
256 views

DB stored procedures: how do they help increase security in web systems?

The usage of Oracle PL/SQL procedures for controlling data access is often emphasized in PL/SQL books and other sources as being more secure approach. I'v seen several systems where all business logic ...
1
vote
1answer
130 views

a question in tables relations

Table A is linked to Table B , and Table B is linked to Table C using primary and foreign keys My questions: Should i link Table A to Table C too?? Or Table B can act as intermediate link between ...
1
vote
1answer
1k views

Derive Attributes in Tables?

I have designed my ERD. It has derived attributes. When it comes to relational data modeling using relational schema, I've designed my tables and linked everything properly. I don't know if I should ...
1
vote
3answers
115 views

Storing summed values

I'm trying to create a database to store values for Profit & Loss statements and I'm wondering what the best way to structure this would be. Specifically, amounts that are are equated from other ...
1
vote
3answers
202 views

Does this design satisfy 3NF, and can any improvements be made?

Does this design satisfy 3NF, and can any improvements be made? If it does not satisfy 3NF - why? By improvements, I only care if it will seriously affect scalability, efficiency, etc - I'm not ...
1
vote
2answers
171 views

What are the pros and cons of this schema for flashcards app?

I am planning a flashcards app with the following schema: user(id, name, email) flashcard_group(id, user_id, group_name, group_tag) flashcard(id, group_id, question, solution) guess(id, user_id, ...
1
vote
3answers
1k views

ER Modeling; multiplicity basics - one to many or many to many?

Apologies for a basic question, but I’m having difficulties determining the multiplicity constraints for a database I’m trying to design. Our team undertakes a number of services/projects...each ...
1
vote
4answers
273 views

How do I develop this data model without a circular reference?

I am trying to develop a concept for a new call tracking system for a help desk. The top 3 levels reference each other. I am trying to determine a better way to link these tables, and am drawing a ...
1
vote
2answers
398 views

MySQL table design / architecture, table is too big

I have a MySQL DB that contains a lot of text, I'm fetching data from a website and inserting it into a table. I'm using a SSD HD (100GB) for the DB and I'm out of space, I think that something in ...
1
vote
1answer
279 views

designing server side database to store user-specific data

I have a database with a number of quiz questions and answers. My basic design consists of these two tables questions (question_id, question_string); answers (answer_id, answer_string, ...
1
vote
1answer
51 views

delimited values in a column value

I have a table which have a * to * relationship with another table. Lets say the first table is T1 and the second table is T2. So I need to keep the T2 Pks linked with single row in T1. I really like ...
1
vote
3answers
182 views

is id and phone number enough to uniquely identify a row in phone numbers

Was designing a table a customer_phone_numbers table a while back, which only purpose was to allow us to store as many phone numbers as a user wanted to have on file. I originally came to the ...
1
vote
2answers
289 views

Managing features for a real estate agency [duplicate]

I have a database table real_estate where the basic information is held (id, status, sku, address, building_type, sales_status) and now I want to attach features to that real_estate table. The data ...
1
vote
1answer
80 views

Have less tables with a lot of data or a number of tables with smaller amounts of data?

Lets say I have a number of tables that will have a type and status and I want the types/statues to be configurable by a end-user of the application (so I can't use enums here). There are 2 different ...
1
vote
1answer
319 views

Using slash in MySQL database

I am using mysql with php Yii framework.When doing data modelling I came accross some doubt.I have a table for user details like this. CREATE TABLE IF NOT EXISTS `tbl_users` ( `id` int(11) NOT NULL ...
1
vote
1answer
64 views

Reading and writing the common data

I have a couple tables and application service that intensively inserts, updates its. From the other hand some client application have to read data using those tables. Sometimes client application ...
1
vote
3answers
121 views

If A is next to B, how to automatically assert that B is next to A

I am working in Access 2010. Let's say I have a database that tracks where packages sit in a warehouse. Table: Packages PackageID (PK) PackageHeight PackageWidth PackageWeight PackageOwner (etc) ...
1
vote
2answers
478 views

database normalization 2nf, 3nf

I am doing homework about database normalization. I have to decompose the table into 3nf form showing the steps here is the initial table: ...