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

learn more… | top users | synonyms (4)

2
votes
4answers
391 views

Does my table violate normalization rules?

I am developing an emulator for a game. My item table looks like this: -- ---------------------------- -- Table structure for `items` -- ---------------------------- DROP TABLE IF EXISTS `items`; ...
2
votes
2answers
5k views

How would you design a table for a booking system?

I have a design database for a booking system. The booking system requirement are as follows: The booking system allows user to book a time slot (1 hour) between 8 am to 1 pm for Monday to Friday. ...
2
votes
1answer
244 views

New table or new column

Apologies in advance if this is a dumb question. Background: I am a student intern, I've been given the task of adding a new feature the companies client administration system. Basically it involves "...
2
votes
1answer
370 views

Using Foreign Keys in database

I am doing a small application for Invoice.In that the tables will be like this === invoice === id (pk) customer_id (fk) invoice_title invoice_issue_date due_date description created_by updated_by ...
2
votes
3answers
499 views

Express union which is dependent on other results

I would like to make a union of two tables that are actually in a hierarchical constellation. How would I write that in relational algebra? Say table A is the parent of B in a 1:n relationship. ...
2
votes
3answers
2k views

View Design Best Practices

When designing a view, would it be best to retrieve the ID fields where FK relationships exist between the joined tables the desired description field obtained through the FK id relationship both? ...
2
votes
1answer
95 views

Is it dangerous to have duplicated entries in a table?

My table have many duplicated entries, is it dangerous if I don't fix this? My table is structured like this: Music_id | Music_title | Music_author | Music_link 1 Look at me now ...
2
votes
2answers
4k views

database schema for a product with multiple categories and hierarchical categories

I need some opinion on whether I am going in the right direction with this. I am trying to build a e-commerce site (with MySQL backend) where a single product can belong to more one than category ...
2
votes
2answers
391 views

Will this Database design logic work and what should be used?

A few weeks ago I had a client of mine ask me about how complex it would be to develop a custom CRM application similar to the one being provided by highrisehq.com What he essentially wants to do is ...
2
votes
1answer
56 views

SQL Server 2008: how do I hide data from a DB Maintainance User?

Our organisation employs a third party company to do maintenance on our database. To be honest, currently they act as our DBA's since we do not have one. In order to do the maintenance work, they ...
2
votes
1answer
72 views

Alternate Name of Data Dictionary

What is the alternate name of Data Dictionary? ► Index ► Metadata ► Data ► System Catalog What I know already is, data dictionaries store all the various schema and file specifications and their ...
2
votes
1answer
64 views

Is this table in my ERD redundant? (Image)

I am developing a research article database for a project. My professor approved this design but I feel like the Author's_University table is redundant and that University_ID could just be added to ...
2
votes
1answer
133 views

Table to create Ids for one table to many table relationships

I have several tables that will represent an object that can be commented on. I would like to store all the comments in one table. To relate the other tables to the comments, I was thinking of adding ...
2
votes
3answers
224 views

Table naming and Database normalization

I'm creating a database of simulation results, and am trying to do so the right way. I've attempted to show relationships between data to make sure nothing redundant is thrown in. My current ...
2
votes
3answers
513 views

Running Total: To store or compute

Say I have a product table with the following columns: product -------- product_id name And I have another table to record the transaction (IN/OUT) for each product product_transaction ------------...
2
votes
2answers
3k views

Should login id and password be stored in separate table

I am working on my academic project. I have a table for employee records which have employee id, joining date, designation and other fields. I am trying to implement a scheme in which when record of ...
2
votes
1answer
718 views

Foreign Key NULL vs Foreign Key to an empty string

This is a table for a catalog of medicines. Some have a pharmaceutical brand, others are generic (i.e. they will never have brand information) CREATE TABLE medicine ( id serial PRIMARY KEY, ...
2
votes
2answers
244 views

Is unique index better than unique constraint when an index with an operator class is required

Postgres docs say: The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's ...
2
votes
3answers
446 views

Which design to choose

Suppose I have a website that communicates with a database. In some page of my site i want to exhibit a list with all the students in my database. There are two kinds of students: regular ...
2
votes
1answer
325 views

How to model schedule and keep good constraints?

I have some data that I am having a hard time modeling. Particularly, I am unsure of how I can ensure that I have unique data. This is for a schedule where I am assigning teams to stores. A team will ...
2
votes
2answers
644 views

Best practice database design - user rating tables

I want to create a table to store user ratings of items for collaborative filtering algorithms. So far my table looks like this: IID | UID | Rating i.e. item ID |user id |rating. From ...
2
votes
3answers
774 views

Removing chat history for a particular user from database

I'm currently developing a web chat application using SignleR and SQL server this is my database schema so far I've added an option where the user can Clear Chat history for a particular ...
2
votes
2answers
567 views

What is the advantage of using numeric primary keys in a relational database?

Many tables in a relational database system (in my admittedly limited experience) have this sort of structure (Oracle syntax as that's what I'm used to): create table widgets ( widget_id number ...
2
votes
1answer
326 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
2answers
256 views

Relations between a imeid, sim chip, and account from a cellphone company?

I've never really had a cellphone (that I owned the account to...long story), but at work we have them, and we want to get a series of relations between accounts, devices (mifi's, tablets, cellphones),...
2
votes
1answer
355 views

What's better here - a single column or multicolumn primary key?

Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to ...
2
votes
1answer
228 views

MS Access + SQL Server for 1160 users - Is it worth it? (Please Help!) [closed]

Our school in Delhi (India) wants to develop a professional and robust in-house database (SIS) that should handle vast amount of student information, including admissions, registration, re-enrollment, ...
2
votes
2answers
374 views

Performance effect of indexing foreign keys

My database is for an HR application with performance reviews and I am interested in whether I should index my foreign key. There are two tables involved. The first is a ratings table with 2 columns: ...
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
590 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
890 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
606 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
236 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
905 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
2answers
2k 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
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
996 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 er-...
2
votes
1answer
553 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
354 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
887 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
619 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
80 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 features ...
2
votes
2answers
75 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
61 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
227 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 ...