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

learn more… | top users | synonyms (4)

0
votes
0answers
23 views

Data Model for a popular/basic type of social app

I am trying to learn about Google Datastore and creating a basic application to have some practical experience with it opposed to just reading the docs. I would like to create a basic social ...
5
votes
2answers
157 views

Dog Adoption Database Design

Apologies if this isn't the norm, but I am seeking advice for the database design for 'dog rescue/adoption'. I've done my normalization research and this is my first attempt at database design. Any ...
0
votes
0answers
61 views

“mysqld got signal 11” error

I have a MySQL 5.6.22 InnoDB database and just now, suddenly out of nowhere, when I try to start it up using /etc/init.d/mysqld start, I see this error in the error log and the server won't start up : ...
-2
votes
2answers
76 views

Random numeric string as a primary key - security, performance, efficiency?

I'm building an app aimed to store lots of records (millions) and trying to find a best way to store records identifiers that is publicly accessible from the web (record URL). I'd like to get: ...
1
vote
1answer
38 views

ERD modeling organization and roles

I am trying to model the following scenario: -I have multiple company bureaus. -Each Bureau has multiple offices -Each office has multiple divisions -Each Bureau has employees that only ...
2
votes
3answers
51 views

What is the best practice for treating fields with only two possible values?

A column/field will only have 2 possible values, either "Transplanted" or "Direct-seeding". Is it better to create a table for these options and give them unique IDs or are they better off put as raw ...
0
votes
0answers
20 views

Do I need to create models if im not going todo anything with that data

I am building a online golf team entry form. I am getting confused as to what models I really need. Basically there is a few leagues and zones(divisions) and in each zone there are 8 fixtures and at ...
0
votes
0answers
32 views

How do I design a database for a registration system?

The concept is something like this: Organiser creates event. User registers for events. Events can be for multiple days. One event can have different competitions. User can enter into any ...
2
votes
2answers
100 views

Does a composite primary key break the first normal form?

Does having a composite/compound key (using multiple columns/attributes) to form the primary key break first normal form? (I am assuming that this is handled by the DBMS, and so the attribute ...
2
votes
1answer
72 views

How to maintain Database audits?

I am using MySQL database (with Django ORM). I want to maintain the database audits similar to StackOverflow, Quora, Wikipedia, etc. These websites maintain the revisions of the changes in the ...
0
votes
2answers
45 views

What is the most efficient way to make a Locality Entity (Town, City, Village etc) with a Country Entity?

I am new to Database. I have a database with a UserProfile Entity. I need to add a Country and Locality(City, Town, Village) to the UserProfile. What is the most efficient way of doing so? This is ...
1
vote
1answer
28 views

Need help making a sane database schema

I am writing a backend for an application that has 4 main types of content: a message, a post, a thread, and a thread reply. Right now, these are all stored in separate MySQL tables: a table for each ...
1
vote
0answers
28 views

Multiple person roles in database(how to define if same person)

I have the following data structure: For internal purposes I can register my outside partner. In this case I don't know his/her identify card number. So I add my partner to database but without id ...
1
vote
2answers
54 views

Why not have Persons for every person in schema?

I noticed that a lot of database schemas often show like, "students" or "teachers" or "employees" or "passengers" or something to that affect. Why not have a schema where every individual is a ...
0
votes
1answer
30 views

designing database where users can have multiple positions

I am trying to determine the best model for the following case: There are multiple offices Each office has multiple staff, each of which have a role (chief, deputy chief, etc) Each office also has ...
2
votes
1answer
34 views

Table attribute for sorting

I have 3 Tables, a User table, a Tracks table and a Likes table. Users can like as many tracks as they want and one track can be liked by multiple users. In my Likes table I have two foreign keys to ...
1
vote
1answer
54 views

Discover missing foreign keys and/or indexes

We have a pretty big database (around 700GB of data), which has been inherited to us, and has had several DBAs. With time we've realized that there are several missing foreign keys and indexes. I'd ...
1
vote
1answer
51 views

Use only one Junction Table per Database?

I am currently working on a Database-Design for an insurer. Example-Entities are "Customer", "Address", "Vehicle", "Building" etc.. So there are many Relations and every Relation has some Context. ...
5
votes
0answers
119 views

Why should I “Avoid partitioning dimension tables” on SQL Server?

SQL Cat have a list of tips titled Top 10 Best Practices for Building a Large Scale Relational Data Warehouse. Under section 4 - Design dimension tables appropriately they state: Avoid ...
2
votes
2answers
69 views

How to join to a single list of IDs from two different tables

I have worked with T-SQL in multiple DBs, but I have never designed a database before. I am designing a new database in MySQL which will track humans and organizations, including financial ...
0
votes
1answer
38 views

How to design a table for reconciliation

A Bank B sends a set of transactions to Bank C every few minutes. Same transactions could be sent multiple times (Bank C should recognize those transactions and should not process them). At the end ...
2
votes
1answer
58 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
2answers
87 views

Does any SQL engine have navigation support in CHECK clauses?

Imagine a simple database to track personal spending. The most basic form of the model should let you create any number of accounts and any number of transactions (specifying the spending amount, date ...
0
votes
1answer
72 views

CALCULATE TOTAL NUMBER OF ESTIMATED HOURS [closed]

I had a question regarding a small task I've been assigned. Below I have pasted the question and a screenshot of the database layout. Now I just don't understand what exactly I'm meant to create, a ...
0
votes
1answer
40 views

Table design for a dictionary that can have words with many different spellings

I'm working on a small, personal dictionary database in Microsoft Access (the 2013 version). There are a lot of words in English that have two or even more spellings. Realistically speaking though, ...
1
vote
0answers
38 views

Alternative to EAV to store data with an unknown structure

For my bachelor thesis I have to build a reseach database which has to meet the requirement of provenance. In the present case it means that among other things I have to store the results of my ...
1
vote
2answers
54 views

Database design for millions of posts

I have this issue. I have three types of posts a user can generate (some users can generate all some can only generate one type). I have the following types: main, employee and admin posts. I ...
1
vote
0answers
33 views

is this normalization correct

is this normalization correct for UNF to 3NF
1
vote
0answers
23 views

Storage solution for realtime time series analytics

I'm designing a system which allows user to analyze their data over time. It is like a IoT database which stores a lot of information from different devices from the user. I don't think that a normal ...
2
votes
1answer
46 views

Constraining foreign keys to two parent tables

For the model above I want to make it so a robot must have a power supply and motor with matching voltages. Is there a way to constrain the robot entity so it cannot have a power supply and motor ...
5
votes
4answers
269 views

Is it possible to get rid of so-called 'Spurious Tuples' completely?

Is it possible to get rid of so-called 'Spurious Tuples' completely? For example: In this example from a textbook, there is an original table: I don't see anything wrong with the two projections ...
1
vote
0answers
38 views

Link between value in one table and value range in second table [closed]

In my DB I have a table that has a field with a numeric value. This table must have a relationship with another table. The first table value will fit a value range in the second table, like this: ...
0
votes
1answer
45 views

Database design for products with multiple units

I am designing a database for retail business. There are some products that can be sold in multiple units, for example, pencils can be sold in ea and dozen, paper can be sold in sheet, ream, and ...
0
votes
1answer
39 views

Designing a database where records can reference different foreign keys

I can use PostgreSQL or Microsoft SQL, whichever provides the needed capabilities. I need help designing a database that has records that can reference FKs in different tables. Given the following ...
0
votes
1answer
27 views

Table design: paired values of genetic data

I'm trying to decide the appropriate way to design my tables to store my genetic data. Basically, I have samples, and each sample has multiple loci (a locus is basically a point on the genome), and ...
0
votes
1answer
50 views

One table to rule them all, or thousand smaller?

I have 10k users in my MySQL database and for every user i need to store the last 500 events. Please advise me, how I should design my database? One MegaEventsTable or dedicated tables for every ...
0
votes
1answer
37 views

MySQL - how to store “other”?

How to store "other" text? In summary, I have too many of these kinds of fields (like hundreds?): http://i.stack.imgur.com/0H81O.png I don't know an appropriate way to handle this. If I store only ...
0
votes
0answers
103 views

Table design for a simple messaging system (ala Facebook or whatsapp)

I'm trying to build a very simple messaging system: a list of conversation with shows the profile pic of my contac, the last message of that conversation and conversation not read will be ...
3
votes
3answers
301 views

Programmatically finding minimum set of fields necessary to create a unique composite key

I am importing flat files from different sources into tables in SQL Server. I am creating a composite primary key using a combination of fields from the extracts that will give me a unique key for ...
4
votes
2answers
104 views

What could justify a non explicit - non atomic primary key

I'm working as a "guest" dev at a random company. I'm working with a table that does not have a primary key by definition(Oracle). The column named "SOMETHING_ID" is varchar2(10) and constructed at ...
0
votes
0answers
29 views

What is database storage structure?

I'm curious that there is a term "storage structure" in the context of databases, but there is no clear definition to be found in the textbooks. Wikipedia ...
0
votes
0answers
38 views

How to map this ER model to actual tables?

Suppose we have an ER-design problem like this: A pharmaceutical company sells its products throughout a country. For the ease of sales, the entire country is divided into some Areas. Areas have ...
0
votes
0answers
16 views

Please help modeling this basic scenario

I've already asked in stackoverflow but I think I was not clear enough and I couldn't get the answer I was looking for I'm trying to modelate a situation and I have a lot of doubts about wheter the ...
1
vote
1answer
33 views

Multiple increments in table

Let's say I have a table which stores different bills. There are multiple departments using this database and every department needs to use their own incremental numbering for the bills. I do not ...
3
votes
2answers
50 views

How can I get dates that wrap around new year from non-datetime columns?

I have a table in my MySQL database that stores daily data (an entry for each day of the year), but does not use a DATETIME column for this. This is because the data is the same for every year (it's ...
2
votes
2answers
54 views

Understanding relations

Since english is not my first language i find hard to understand this paragraph that i'm reading from "Database Fundamentals" book. I'm trying to figure out, in practice, real world, what this ...
2
votes
0answers
41 views

Any drawbacks to referencing multiple tables from a single column?

I'm working on a system with the following entities: Projects Sections Activities There is one important constraint that needs to be enforced: Activities are always part of one project. ...
0
votes
0answers
58 views

Database design for storing historical stock exchange Level2 (depth) data

I am developing C# software using embbeded SQLite Database which shows historical level2 (market depth - asks/bids) stock exchange data graph. Example Simply speaking, there are 25 asks (price, ...
1
vote
1answer
49 views

SQL Schema Change; Modification of Existing Table vs Creation of New

We are planning the data schema for our project. Currently we have a table with classifications, but there is the possibility of a new classification in the future which would require a new field to ...
1
vote
1answer
41 views

Need help on database design

I have a huge database of library system but I have a problem on how to design tables on this database. This is because I have under one entity (for example AUTHOR) a lot of repeatable fields. So I ...