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

learn more… | top users | synonyms (4)

2
votes
4answers
5k views

Database design - People and Organisations

Databases are not my primary skill and I am finding I need some assistance please. The software we are building has "Customers". The Customer could either be a Person or an Organisation. I really ...
2
votes
1answer
576 views

Table for optional parent/child relationship

Assuming we have the following table: Item, Parent, Child and Parent is the parent of child. The item can either belong to a parent or child and not both. I have other tables that are similar to ...
2
votes
1answer
867 views

Best practice around sys.dm_db_missing_index_group_stats in SQL Server

I have a fairly simple query using sys.dm_db_missing_index_group_stats that identifies missing indexes in my SQL Server database. This is pretty commonly used. My question is how do I identify which ...
2
votes
2answers
598 views

Relational database for address model

I want to design an "Address" model for all types of entities like users, businesses, etc. I have two types of main models: one is User and the other is Business. Each one has different address types ...
2
votes
1answer
134 views

Is there any reason that metadata should not be a part of my database schema?

By metadata I mean things such as: the owner of an entity permissions associated with an entity the date and time of data entry the surety of entered data the format of a text attribute descriptive ...
2
votes
1answer
167 views

Databases design decision - Mobile & Web

This may be a question a little broad but... I'm currently having the following dilemma: Having a web application (already deployed) and a new mobile app, I'm currently working on the mobile app ...
2
votes
2answers
1k views

Closed loops in database model, does it affect the performance?

I have heard that having loops in the database relational model should be avoided. From what I can think of, it is not a good practice, because there will be more than one different pathes to get the ...
2
votes
1answer
1k views

How to set unique columns combination

see this table: CREATE TABLE IF NOT EXISTS `default_messages_users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `message_id` int(11) NOT NULL, `owner_user_id` int(11) NOT NULL, `to_user_id` ...
2
votes
3answers
235 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 ...
2
votes
2answers
902 views

mysql products db design

I am building a products site with php and mysql(no shopping cart). I have 400k products, the main site with 2 methods for query. query for products types, all the query by menu, cloud tags (no ...
2
votes
3answers
4k views

Sorting by DATETIME slower than by TIMESTAMP?

I have the a DATETIME key on which I sort the results and the query is running quite slow. Will it run faster if I change it to TIMESTAMP ? Because I noticed that ordering by numbered keys is done ...
2
votes
2answers
984 views

Logical schema development and migration tool for different physical schema available including triggers and stored procedures?

We are planning to support for our application another rdbms (oracle). At the moment we are design our database schema with the MySQL-Workbench. For every new database version we have schema ...
2
votes
1answer
551 views

Pros and Cons of having additional columns in join table of many to many relationship

I searched but did not find any discussions on this topic. Are there any pros and cons on creating many extra columns on joined table? Does it affect speed of data retrieval because we are joining 3 ...
2
votes
2answers
348 views

Indexing of Data Warehouse tables?

Maybe I have misunderstood something but I can recall reading an article which said that there is no reason to create non-clustered indexes on very large data warehousing tables? That is because ...
2
votes
2answers
878 views

SQL datatype choice for flagging a read/ unread message?

I have a table where I want to track Boolean type operations. in addition, a business requirement is that I know the date of the action and not just the Boolean value. for example if a message is ...
2
votes
2answers
612 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 ...
2
votes
1answer
76 views

Many to many between lots of tables?

TL;DR: How could I set up many-to-many relationship between that many tables and is it viable/recommended? Each body type has its own table Each car in each body type table can has several ...
2
votes
2answers
71 views

Is better to create a table for each room or make a single table that handles all the rooms in my case?

I need to make a server-side for iOS and android application that does something similar to chat(Is not really a chat), and I'm a PHP and MYSQL newbie. The process is that 2 users join a room and do ...
2
votes
1answer
48 views

Adding foreign key that references a column which is not Primary or Unique key

I got into trouble when I want to limit values of Table A's column to be taken only from a set of optional values in Table B's relevant column. Problem is table B's relevant column isn't a foreign ...
2
votes
2answers
60 views

Update a table after insertion to another table in PostgreSQL?

Let's say that my database has 2 tables (Person & Car): Person (personID, number_of_cars) Car (carID, owner) where the owner attribute in Car refers to the personID in the Person table. Now, ...
2
votes
1answer
215 views

A good use case for Postgres JSONB?

I am looking to create an Asset Management System to track assets (laptops/desktops, monitors, keyboards, mice, bags, software keys, etc), basically anything that can be assigned to an employee. My ...
2
votes
2answers
820 views

Modeling invoices and orders

I have a problem in modeling a database that contains orders and invoices. Actually it's more of a problem from a business point of view, but it's related to modeling the database nonetheless. Model ...
2
votes
2answers
186 views

How to review Database schema?

I am green been software engineer, my team lead give me a task in PostgreSQL database, I need to determine the relationship of the all 405 tables, but the problem is the database doesn't have any ...
2
votes
2answers
106 views

Is it ok to have 40+ columns in a table

I am currently re-designing some of DB's for a project. There is one DB in particular that is giving me a little of a headache. The idea is that for every client_id and date (client_id + date would be ...
2
votes
4answers
395 views

Can't finish my ER diagram

I am trying to create a database for a small book library. This is not a professional project, just an exercise of my own. This is my first time to try this ( I am self-taught ) and I am encountering ...
2
votes
1answer
3k views

Execution plan index suggestion - difference between similar queries

I am trying to analyze queries on this table: CREATE TABLE [dbo].[Values]( [tid] [smallint] NOT NULL, [t] [datetime2](3) NOT NULL, [v] [real] NOT NULL, CONSTRAINT [PK_Values_Unique] ...
2
votes
1answer
105 views

Conditionally join table

I'm new to database design and have designed a database that seems fine for the application but looks like it has a major red flag to me. I have a master table which is then joined to another table ...
2
votes
3answers
322 views

Entity Relationship Model - double one to many

ERD A customer submits one or many leads. A lead belongs to one customer. A lead is a request or an inquiry. A lead belongs to one manufacturer. A manufacturer has one or many leads. Tables ...
2
votes
1answer
319 views

Clustered composite key high fragmentation and fill factor

I've recently made a table to hold the language preferences of my users as follow: CREATE TABLE [dbo].[systemUserLangPreference]( [systemUserID] [int] NOT NULL, [langID] [int] NOT NULL, ...
2
votes
1answer
102 views

Confused on how a value gets added with the DEFAULT constraint

A recent definition I've read about Default Constraints: DEFAULT is specified for a column to assign a column a specific value if no value is specified when a row is added. I believe I've seen code ...
2
votes
1answer
113 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 ...
2
votes
1answer
315 views

Database design for storing historical nmap data

I am designing a database to store nmap data. I want to store two basic types of data: Information about the nmap scans themselves (e.g. start and end times, nmap version used, command line ...
2
votes
1answer
305 views

Need help to design model with multiple rights

For a website, I have multiple elements that need to be moderated by many different users. Until now, I have always used Role-Based Access Control (RBAC) model but I want a more flexible model. I ...
2
votes
1answer
122 views

How to enforce a nullable foreign key?

I have a relation between two tables. The foreign key table can have a row related in the primary table. There's a way to enforce the value of the FK column to be NULL or one of the values of the PK ...
2
votes
1answer
49 views

Relational Database tables

I have a table Player where playerID is the primary key. Then I have a table Game where gameDate is the primary key (of type DATETIME). Each game is played by 2 players and each player can play many ...
2
votes
2answers
432 views

Expanding parent-child tree with additional tables

I have readonly access to a table (Oracle database) which contains a hierarchical structure, represented via parent-child. I need to add another table, which will expand the existing hierarchical ...
2
votes
1answer
5k views

one trigger for multiple tables

i have a trigger in PostgreSql 9.1 that i want to fire on inserts of several tables. is it possible to have it affect all these tables instead of creating the trigger for all these tables? i have 58 ...
2
votes
2answers
455 views

Support ticket system - when/how to move old tickets?

I don't have access to the partitioning feature, but consider a support ticket system with tens of thousands of tickets being opened everyday and taking about a week to a couple of months in getting ...
2
votes
2answers
85 views

Referential Integrity - Re-using table

This is a simplified example of a design issue that I am facing: I have 3 tables: Car, Ship and Bicycle. I need to add an "activity logging" table that records user actions such as deletion and user ...
2
votes
2answers
933 views

Should I use SQL or NoSQL for this specific design (surveys)?

Building a small web application to create satisfaction surveys with a default list of questions and responses. The application is for a small audience of people (5,000 max) with surveys sent out when ...
2
votes
1answer
6k 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 ...
2
votes
1answer
110 views

What are practical hurdles in making protein interaction database?

Following are some protein interaction databases: http://string-db.org/ http://dip.doe-mbi.ucla.edu/dip/Main.cgi As a newbie what are the practical aspects of database that I must understand before ...
2
votes
2answers
303 views

Writing to a DKNF database, but reading from a de-normalized table for performance?

Would the following approach be reasonable? The Goal: queries have to be relatively fast, but without the hassles incurred by writing to a de-normalized database. The Context: A database for ...
2
votes
1answer
501 views

Oracle ash report

Recently, i have been handed over with an ash report from DBA. To me this report is like french. I have no idea what it is about and what all is written in this report. Can someone please guide me in ...
2
votes
2answers
1k views

Joining based on date (or date range)

I'm not sure if I've completely fudged the design of my small hobby database (I'm not a DBA by any means), but I have a table like this (primary key is (staffid, effectivefrom)): staffid | target | ...
2
votes
1answer
524 views

Design problem with auto-increment fields

I've made this design similar to my problem, it's not exact but it close enough to understand my problem: http://i42.tinypic.com/bhbp5.jpg Some cellular client frequently send their GPS position and ...
2
votes
1answer
149 views

How to make a table with fields that are other tables in MySQL?

I am making a music library system with php in MySQL. In this system there would be a table named Songs. Since many songs can be by the same Artist (Person), I wanted to store a table of Person that ...
2
votes
1answer
860 views

Separate Schemas by Industry or by Application functionality?

I currently have a small retail/Point of Sale and an Education application running as on-premise custom solutions for different customers. I am planning to rewrite the app and host as a SaaS/web ...
2
votes
2answers
6k views

Primary Key on a View

While answering this question How to add a Primary Key on a Oracle view? A lot of questions popped in my mind. 1. Is it advisable to have primary key in a view. 2. Why anybody be intrested in ...
2
votes
4answers
177 views

Is there a “standard way” to log network messages (events) into a database?

I have two applications communicating over a LAN via events contained in network messages. Each message (i.e. an UDP packet) contains only one event. Messages are heterogeneous, meaning that the first ...