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

2
votes
2answers
211 views

design of maintenance database

Lets say i am designing a database that tracks maintenance of equipment. Each piece of equipment has various fields that usually don't change eg. ID, model, serial number etc. Some fields change ...
2
votes
2answers
96 views

Is it safe to use “Safety” as a schema name?

I was considering using "Safety" as a new schema name in SQL Server. In SSMS, the term Safety is colored as a keyword, but this term is not listed as a reserved keyword in the BOL. The database does ...
2
votes
1answer
97 views

How to make a duplicate field cheap?

There are two tables associated with each one to many in a field. In Table 2, there are fields that duplicate the value in Table 1. Is there a cheaper way to ensure (guarantee) the correspondence ...
2
votes
1answer
1k views

Track daily view count of youtube videos

I am making program that searches youtube for given keyword and need to get links of active videos. Term "active" means that video must have 1000 views per day. Youtube API doesn't give such ...
2
votes
2answers
84 views

Importing order information

Our new project is to write a customer order management system. My specific task is to import customer orders from various different sources - XML, CSV, XLS... etc. Our old DBA has left the company ...
2
votes
2answers
895 views

How do I properly design a many-to-many (charges/payments) accounting system?

This is just as much a db/programming question as an accounting/math question, so I'm actually posting to all relevant boards. I appreciate your patience in advance. Basically I'm having trouble ...
2
votes
1answer
680 views

Question on Design for User + User Groups + User Types + Subscriptions Model

I am redeveloping an existing web application with both weak source code and a weak underlying database design. I'm trying to develop things in a way that future expansion will be much easier, ...
2
votes
1answer
117 views

Foreign Key Constraint over two tables

I am trying to put the following entity-model into practice: We have a list of Payments, each Payment belongs to a certain store and is of a certain payment type (e.g. 10€ maintenance cost in ...
2
votes
1answer
54 views

Migrate existing SQL Server database file to smaller files

I have a database that is about 1TB in size. It's currently on a 2TB hard drive and I would like to replace it with a few 512GB SSD. I need to split the existing database file (.mdf) into multiple ...
2
votes
2answers
56 views

Three-sided many-to-many relationship design

I have 3 entity that have a relation to each other. My entities are: OPTION PLAN PRICE Relationships: PLAN and OPTION have a many-to-many relationship Map of PLAN and OPTION has a many-to-many ...
2
votes
2answers
49 views

Database design appoach confirmation

I am working for a mortgage company in migrating a legacy application to new application for managing claims, means lender fore close a loan and request for a claim to get its funds from government or ...
2
votes
1answer
98 views

Personal finance database design

I'm making a personal finance management application in PHP and MySQL, for learning myself some more PHP and MySQL. This system is currently using 4 tables: users, this table holds all the user ...
2
votes
1answer
77 views

Database Design - Removing ambiguity without adding loop

I'm looking for a better approach to the problem (and my solution) described below. I've attached an image of my initial design Let's say I have a cat, and I'd like to find its owners and what vets ...
2
votes
1answer
39 views

How to relate a table to other two?

I'm trying to design a database that contains, among others, entities "Photo", "Venues" and "Products". Given both Venues and Products could contain a collection of photos... how could I tell whether ...
2
votes
1answer
40 views

table structure for events

Got a bit stuck, need a hand. I need to store events at the database, and events may happen at different location multiple locations at the same time so these are my tables CREATE TABLE ...
2
votes
1answer
64 views

Table structure for voting app

I have an app where a user votes between two local profiles on which one is better. I want to ensure that the same user cannot vote more than once between the same two profiles (say the user votes on ...
2
votes
2answers
204 views

Is it acceptable to have circular foreign key references\How to avoid them?

Is it acceptable to have a circular reference between two tables on the foreign key field? If not, how can these situations be avoided? If so, how can data be inserted? Below is an example of ...
2
votes
2answers
60 views

unusual relation, binary aspect

I have question very unusual for databases. I’m not a good English speaker, but good English reader :) so for explanation I’ll give you examples. Part1 In MySQL, I have two tables with relations ...
2
votes
1answer
65 views

Scalable Relational Database Design

I'm designing a web application (using Django) for a client that will need a fairly large database behind it. I've never worked with databases that handle large amounts of data before so I'm hoping to ...
2
votes
1answer
79 views

How to enforce an “exactly-one-per-group” constraint?

I have a table x that I've defined like this: CREATE TABLE x ( xid INTEGER NOT NULL PRIMARY KEY, yid INTEGER NOT NULL REFERENCES y(yid), is_principal BOOLEAN NOT NULL ); This definition ...
2
votes
1answer
107 views

I can't create relationship in my database sql server

I have designed a database for my website. It has 3 tables: user, topic and comment. A user has many topics (or articles) A topic (or article) has many comments A user has many comments I ...
2
votes
1answer
78 views

Does SQL Server redistribute data after adding an additional data file?

Imagine a database about 30GB in size using 3 data files on the same disk. Someone told me to have one data file per processor implemented to get full performance on all cpus. So if I now add three ...
2
votes
2answers
186 views

What's worse, too many entities or too many tables?

I just had a question about database design, I am in fairly early learning stages of this so bear with me. Suppose I have a parent table called "Projects" each "Project" Entity can have any number of ...
2
votes
2answers
113 views

Associating on the ID of an association table

Sorry about the title, I couldn't find anything better. Suggestions are welcome. Say we have two tables, Suppliers and Products. The same product can come from many different suppliers, so we create ...
2
votes
1answer
312 views

Dependency preserving and lossless join decompositions

I have a relation: R = {A, B, C, D, E, F, G, H, I, J} And a set of functional dependencies: G = {AB -> C, A -> DE, B -> F, F -> GH, and D -> IJ} I ended up decomposing into two ...
2
votes
1answer
42 views

Database table to keep track of objects

I have a table called FoodMaker and another table called Settings. Every FoodMaker has multiple Settings. I also need a 3rd table to record what has been made, when and with what Settings. It might ...
2
votes
1answer
93 views

Supertyping Inventory Transactions

I am trying to wrap all interactions with inventory items into one transactional table. Currently I calculate QoH by querying multiple fact tables (production, acquisition, shipment, return order, and ...
2
votes
1answer
83 views

Hierarchy for dimensional model

I am trying to model a DW where I have many levels of geography (Neighborhood, District, City, State). I have several demographic data that should be included in the model. These data include count ...
2
votes
1answer
242 views

How do I add versioning to this CMS I'm designing?

As you can see, my CMS consists of several widgets (for example an Articles widget), of which you can create several instances, which I call blocks, and those blocks are linked to pages and the ...
2
votes
1answer
198 views

Table income and expense is better to have it together or apart

I am developing a small system where I have to record incomes and expenses of the company, and even I can not determine if the income and expense table should be in one table or in separate tables. ...
2
votes
1answer
209 views

Optimal table design for a medical center rental service and recurrent shifts

I'm trying to figure out the optimal design for the following situation I'm being presented. I'm developing a medical web application with MySQL as db engine. A medical center has 14-16 offices/rooms ...
2
votes
1answer
103 views

Review database schema / thought process

Please review my thoughts below. It is written in an authoritative style because it is the most succinct, not because I am competent. Please poke as many holes as possible. I am trying to create a ...
2
votes
1answer
464 views

How to represent a many to many relationship with Crow Feet diagram

I'm designing a database and I want to represent the relationship between users and groups: A user can be on zero or more groups. A group can have one or more users. Using Microsoft Visio 2013 I've ...
2
votes
1answer
138 views

Confusion normalizing data to 3NF and transitive dependencies

I am currently in a Database class and have some confusion with normalizing to 3NF. I have one specific issue that was not addressed in class but appears in the book problems. For example I have R ...
2
votes
1answer
41 views

How can I model and enforce constraints on a categorical item contained in a collection?

Scenario: There is a collection of items where each item belongs to 1 category. There is a collection of buckets which may contain at most 1 item from each category. Oh, and an item may belong to at ...
2
votes
1answer
168 views

Best way to store an unordered list? [closed]

I apologize if this question has (undoubtedly) been asked before, however I've been hearing conflicting advice from colleagues and the internet. I have a table of "groups" and each group has a list ...
2
votes
1answer
509 views

Should I save “document ID” as a String or Number in MongoDB

So I'm importing about a 100,000 documents into mondoDB. MongoDB already generates an Object hash but the data has a documentID that was used to identify records in the previous database. The IDs ...
2
votes
3answers
606 views

Uniqueidentifier vs. IDENTITY vs. Material Code --which is the best choice for primary key? [closed]

Which one is the best choice for primary key in SQL Server?There are some example code: Uniqueidentifiers e.g. CREATE TABLE new_employees (employeeId UNIQUEIDENTIFIER DEFAULT NEWID(), ...
2
votes
1answer
64 views

Table Relationship cardinality

I am designing a database for my application, I got issue in implementing the following logic. A user may be a Vendor and also a Buyer. I have created a table for USER and created an another table ...
2
votes
1answer
232 views

Help designing tables for storing lab test results: values include numbers, <number, >number

We're using MySQL 5.5 and attempting to design tables to store the results from tests run in a lab. The results of some of the tests can be either a plain number, e.g. "5", or a range such as "< 5" ...
2
votes
1answer
67 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 ...
2
votes
1answer
258 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, ...
2
votes
1answer
228 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 ...
2
votes
1answer
544 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 ...
2
votes
1answer
151 views

What are the risks for logging across databases via a trigger?

I've searched for this online and had mixed or unclear responses, and after posting on superuser, was directed over here. On SQL Server 2005, we currently log certain table changes via triggers using ...
2
votes
3answers
1k views

Database Design Relationship for Patient Visit in MS Access

I am designing a database to store records for research purposes, and I am having trouble picturing how I can create a relational database based on the information I have. There are a total of 6 ...
2
votes
2answers
540 views

How to design future proof tables?

I am new to database design and have started developing my first schema. Some of my tables (e.g. user_profile) I am struggling to come up with an exhaustive list of fields for the table. A particular ...
2
votes
1answer
219 views

How to design functionality to comment about a user joining a Facebook group?

User A joined the group DBA The table responsible for users x groups is "Following" table. user_id group_id 1 1 1 2 2 1 And ...
2
votes
2answers
148 views

What is the way to paginate last updated rows from multiple tables?

I want to have something like a newsfeed from multiple objects sort by most_recent. However I need to paginate (load more/offset) the results. Row1 = Post's - text, username Row2 = Photo's - text, ...
2
votes
1answer
990 views

How to model a user/group relationship where users are assigned an unique number within the group?

I am working on a web-server applications that deals with 2 entities: users groups A user can belong to one, several or no group at all. Fairly usual, I assume. However, I have additional ...