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

learn more… | top users | synonyms (4)

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
98 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
71 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
33 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
118 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
68 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 ...
1
vote
1answer
57 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
53 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
266 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
44 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
102 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
103 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 ...
0
votes
1answer
46 views

How would you design a table for approval system between User and Sports Club

I am currently learning database modelling and struggling to model the following requirement. I have a user and sports_club table. User can only join a sports_club if three existing users of the club ...
0
votes
1answer
63 views

SET or VARCHAR for list of tags in a MySQL photos table?

I just discovered the SET column type for MySQL tables. I have a photos table with ARIA engine where I want to store tags/keywords for each photo. Now I use a VARCHAR type and then find the photos ...
0
votes
0answers
16 views

build users availability report/Table (using sql)

I got a question from our product team to build a "Table availability of customer service representatives (user)". f For example : user_A was available to accept a call from 12:00 to 12:03, so I have ...
0
votes
0answers
19 views

Structure of an SQLite database for storing analytics with counters and hits

Before making this post, I referred to this and this which contain extremely complex solutions and hence, this question: I am building a spam filtering app that takes posts from a feed and deletes ...
0
votes
1answer
18 views

How can I differentiate between a Multivalued Attribute and an Entity?

While designing ER Diagrams, how can we differentiate between a Multivalued Attribute and an Entity? How can we know that, what we are considering as an 'Entity', isn't actually an entity but a ...
0
votes
1answer
57 views

Normalization from UNF to 3NF

I keep getting confused trying to normalize this data. A company makes and sells a product online. There are ten types of Products, each having a code and price. Their Sales Order department takes ...