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

learn more… | top users | synonyms (4)

1
vote
1answer
58 views

Should I look into alternatives to a relational database? [closed]

I am currently in the process of going through some PHP training with procedural code and will be doing a course in OOP with PHP right after that. The course will provide a solid starting point for me ...
6
votes
2answers
87 views

m-to-n total participation in SQL

How should I model the following design in SQL? As it's shown, every A must have at least one B, and vice versa. I know I should have three relations: A(a), B(b) and R(a,b), but what about total ...
4
votes
2answers
58 views

MySQL - Order by closest value on multiple columns

I have a table for a game lobby. In this lobby a user gets put in their connection and their level. In my SQL statement I am getting all the users in the lobby which are between +5/-5 of the user's ...
1
vote
1answer
58 views

Efficient way to store multi choice in a column with few options

I am looking for advices on how to handle multiple choices in a column? By way of example lets say selecting a fruit for lunch. Options are Apple or Pear. And you are 100% sure those two options ...
3
votes
1answer
66 views

ER Diagram - subclass Entity constraint

Can someone help me out on how to model this situation: 1) Superclass PRODUCTS with subclasses FOOD and SHOES. 2) Another superclass CUSTOMERS with subclasses SHOE STORE, GROCERY STORE 3) I need to ...
1
vote
1answer
36 views

Database agnostic auditing [duplicate]

Background Various databases have custom implementations for auditing: SQL Server Audit Oracle Audit PostgreSQL Audit Trigger MySQL Enterprise Audit Problem A number of problems exist: There ...
1
vote
0answers
38 views

What is a good database design approach for my Online Quiz Android Application?

I have been working on an Android Online Quiz Application with PHP & MySQL. I know what attributes i need however when designing the relationship it becomes complex and confusing when I analysed ...
4
votes
1answer
47 views

2NF Normalisation

I'm using a past exam paper for practice, can someone help me normalise this database to 2NF? I have uploaded a picture of the original below, and also my attempt at normalising it myself. I am ...
1
vote
0answers
28 views

Design improvement on multiple references

Im having some 'perception' problem, maybe... I got this table linked multiple times with another small table and maybe is more of a personal problem with the way it looks but I feel there might be a ...
2
votes
1answer
54 views

SQL Query for Internal Messaging System

I'm creating an internal messaging system (No SMTP/POP3) and I'm looking for a way to query a user's list of messages. Here's my schema: Messages _MessageID (PK) _MessageParentID (if a reply this ...
0
votes
0answers
32 views

Relationship between favorite and contact in database

I am making an E-R diagram for my contact project that simply has contact and favorite as tables. Both of them has the same attributes(ex:id,name,number). What is the relationship between such ...
1
vote
5answers
145 views

Should I have a column for every data type or 1 column that stores everything

I have a database table that currently looks like this: PageField -Id (Int, PK) -FieldType (String) Can be Text, Decimal, Integer or Bit -Value (This stores the value regardless of the FieldType, so ...
5
votes
2answers
341 views

CREATE as SELECT with CONSTRAINT?

I have a piece of SQL that creates a TABLE form a SELECT statement in PostgreSQL ; I want to add constraints to the table so that for example a column cannot be null. I cannot find valid SQL for this ...
0
votes
2answers
49 views

should I denormalize for user profiles?

Assuming the choice is to stay with relational model, how are tables structured in the case of large User profiles? All the examples I see consider only simple cases, such as a "profile" containing ...
6
votes
2answers
87 views

Which executes first? Trigger or rule?

Using PostgreSQL. Had a trigger which does something after delete and made a rule which stopped delete from happening if something will be true. My question is: will the rule prevent the trigger ...
1
vote
1answer
33 views

Animals hierarchy use-case: multiple farms, multiple species

Let's talk about an online service where goat/sheep/cattle/llama farmers would register and keep track of their animals. One of the features of such a service is tracking parent/child relationship. ...
4
votes
1answer
31 views

Dynamic form builder database optimization Postgresql

Hi I have a project that create forms dynamically I use a model like the following: forms ----- id (PK) name (other fields) form_fields ------------- id (PK) form_id (FK to forms.id) ...
2
votes
2answers
56 views

Multi level hierarchy relationship

My table design is users (id, username..., parent_id) Data is id username parent_id ------------------------------- 1 | admin | null 2 | reseller 1 | 1 3 | client 1 | 1 4 ...
1
vote
1answer
28 views

Update all rows when sort/ordering value is changed for single row?

I am in the process of creating a GIS application with multiple layers and many pieces of content shapes on each layer. Currently each layer/shape is drawn in the order that it was returned from the ...
0
votes
1answer
38 views

Query other database's INFORMATION_SCHEMA tables

I have a shared database that stores procedures for all the other databases on the same server. This helps maintain one instance of a procedure rather than copying it across all databases. As long as ...
0
votes
1answer
35 views

Joining 4 tables of which one is unrelated

I have 4 tables as follows. I want to find a wholesum resultset, limited to 25 records, in the following way: Users from Table1 based on a condition And those Users should not be in Table2 [Non ...
0
votes
1answer
36 views

Shared entities across multiple databases

I'm currently working on an application with a master database and individual user databases where all user input data resides. These individual databases are exactly the same. master connects the ...
0
votes
0answers
43 views

Updated Financial Database Design

I took the design that @Daniel Hutmacher suggested in question below: Financial database design I amended it as to what I need below: -- Companies CREATE TABLE companies_list ( [code] int NOT ...
2
votes
2answers
302 views

Can different tables have columns with the same name if those columns are not primary keys? [closed]

I know it's a simple enough question but my textbook is very confusing about it.
4
votes
1answer
48 views

Quite large (400k) mysql database design for multiple users

I'm seeking advice of experienced admins. I'm working on a website, where you solve word anagrams. If it solved it should never be displayed again. Wordbase contains ~400k entries. What would be the ...
0
votes
2answers
52 views

Document Store & Relational DB Hybrid. Exists? Possible?

Does anyone know of any product or open source project that attempts to strap together Mongo DB and MySQL for instance? I hear a lot about how relational DBs being ACID compliant makes it nearly ...
2
votes
0answers
22 views

Creating UNF for multiple actions

I am creating a database design for manufacture company for my coursework. The database design should be able to do these tasks: Buy items from suppliers. (M - N) Items are used for making products. ...
7
votes
6answers
849 views

Possible benefits of storing multiple values in one field of one row instead of as separate rows

During our last weekly meeting, a person that has no background experience in Database Administration brought up this question: "Would there be a scenario that justifies storing data in-line ...
0
votes
1answer
38 views

Best to isolate the database in multiple instances?

I read on a forum about the use of database isolated environments are neccessary for any large project that handles "big" data. For instance I will be using the following table for ALL blogs: ...
1
vote
1answer
67 views

Database Design and structure

I want to make colleges search system where a user enter any keywords in a search box and we can provide them some institutes with their details like institutes names, belonging hotspots, cities, ...
2
votes
1answer
42 views

For a software application, does every workstation need database drivers?

really a just a newb question. In the case of something like an enterprise system for a company, where end-users are executing continuous reads and writes to a database, it does not appear that in ...
0
votes
0answers
26 views

DB table design for attributes with multiple entries (eg: Person -> Social Media)

I am trying to decide the structure of a DB table to store Employee data. I am bit confused on how to design attributes such as Address, Social Media etc. There should be provision for multiple Social ...
0
votes
0answers
29 views

What is the db-structure behind sites with multiple similar subdomains, like Stack exchange?

own db for every subdomain 1 big db used by all of the subs own and shared db-s used by the subs When to use the 1st/2nd/3rd approach? Thank You!
1
vote
2answers
36 views

More tables or more columns (in my simple example)?

I'm modelling DB and wondering if I should create more very simple tables or just have more columns in tables. I mean, let's assume I have s table named User, each user can have status(ACTIVE, ...
0
votes
0answers
44 views

Database Design for group chat

I am creating a chat system as a RESTful API. This is what my design looks so far A request can be made to fetch the newest 50 messages that are part of a group id. Even if I do get a list of ...
4
votes
1answer
26 views

Enforcing that a user should have one or more email addresses

I'm trying to design an application where a user can authenticate oneself with one password and one or more email addresses. I'm not sure how to enforce this constraint. The tables so far are simple: ...
0
votes
0answers
35 views

Database calculations question

I am creating a MySQL database that is going to be used to store a lot of timestamps. There are currently 6 timestamp fields that data can be entered for one row. I am utilizing timestampdiff to ...
5
votes
1answer
110 views

Hard delete vs soft delete performance

I've got this table that is intended to have a lot of activity. Anything that's in it indicates that a user action is still pending. Once deleted, it would indicate that action is no longer pending. ...
4
votes
0answers
58 views

Social (Classmate like) database schema appreciation

This is a database schema I propose for a networking website like the Classmates.com web site. Do you think the database has all the tables needed for such a project? What would you rather do? The ...
1
vote
2answers
61 views

Is nvarchar 0-indexed? [closed]

Is the max length configuration for nvarchar 0-indexed, e.g. If I declare column x as type nvarchar and set the max length to (2): (X nvarchar(2)) does that mean that the column takes 3 nvarchars? or ...
3
votes
2answers
39 views

Mysql - Each mysql database with its own user?

I developed a web app in which each company that registers on the app gets its own database with its own mysql-user credentials. Is this approach fine OR can I use one master mysql-user account to ...
3
votes
0answers
42 views

Is this ER Diagram correct?

Requirements: • Each customer has a name, a permanent address, and a social security number. • Each customer can have multiple phone numbers, and the same phone number may be shared by multiple ...
1
vote
2answers
81 views

What are the advantages and disadvantages of Third normal form and Boyce-Codd normal form? [closed]

I know that 3NF makes a database more efficient and stores data according to relevance, which is more logical, but are there other advantages? and are there any disadvantages?
3
votes
1answer
80 views

Versioning data in a database [duplicate]

I have a database to hold data about projects. Projects have lots of data associated with them that is segmented into other tables such as project_contacts and project_images. Those are one-to-many ...
3
votes
3answers
146 views

Choosing a PK: VARCHAR(2) or SMALLINT(2)?

Today we were discussing the following: Brazil has 27 states and each one has its own abbreviation (just like USA). So we've got RJ for Rio de Janeiro, SP for São Paulo, MG for Minas Gerais and so ...
3
votes
2answers
173 views

Database design of Room Booking system in a Hotel

Intro and System Description I am currently designing a Database for a Room Booking system in a Hotel. Client fills a Form / Request with following information about Room: amount of people in room ...
1
vote
0answers
33 views

Multiple columns VS one big-capacity-column?

I have a table like this: // table_A +--------+---------------+------------+ | id | short-text | long-text | +--------+---------------+------------+ int(11) varchar(200) text ...
1
vote
1answer
51 views

Store Mysql Index in separate .Ibd file?

Is there any option to store table index in separate .ibd file? something like file_per_table mode? Becuase my table is growing more than 200gb. Table .ibd file have index for that table also. so If ...
4
votes
1answer
65 views

Checking existence and syncing data with millions of rows

I am developing sms based service platform. In India, we have two different types of routes to send sms - promo & trans. Sms sent via promo route has to be checked against nationally maintained ...
1
vote
0answers
38 views

Review the ER diagram of a University

A database includes information pertaining to students, lecturers (scientists) and lectures. We store first names, last names and correspondence addresses for each person. A lecture is ...