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

learn more… | top users | synonyms (4)

1
vote
2answers
57 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
34 views

is this normalization correct

is this normalization correct for UNF to 3NF
1
vote
0answers
25 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
276 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
49 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
115 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
303 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
105 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
51 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
42 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
59 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
50 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
48 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
64 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 ...
0
votes
0answers
13 views

ER diagram when having a condition

Can someone explain to me or point me to to the link on how to draw diagrams that have conditions. Example : you got a booking website that allows regular people to book or rent a room at some hotel ...
0
votes
1answer
45 views

Single-Table vs. Multi-Table design

I'm trying to come up with a database schema which is going to be used for a social network similar to that of Facebook. Now for posts I have two schemas in mind which are as follows: Single-Table ...
0
votes
2answers
41 views

Define a db before coding

I'm starting to code time tracking application to replace the monthly excel sheets my company is requiring. Its purpose is standard : to centralize all data about the time spent by different(or same) ...
-1
votes
2answers
46 views

MySQL Server Disappeared

I haven't used my MySQL local server in over a year. Today I tried to get in to it, and the server seems to have just vanished. I've been searching a troubleshooting all day and have gotten nowhere. ...
1
vote
1answer
156 views

Hotel Management database

I need help with the design of my schema. I'm almost new at this and I'm not sure whether my relationships between the tables ROOM, GUEST and RESERVATION below are correct. Also, I have problems ...
3
votes
1answer
176 views

Best way to model a single user with multiple usernames

So I'm looking to write a website where a user will login with their email address and password, but can have multiple usernames. The context is a social gaming website. This may result in a user ...
0
votes
1answer
21 views

Should i separate ContactInfo (sub-entity) from MyTable (main-entity) or Not?

I am designing a database of a school management system. I have one to one relationship of MyTable with ContactInfo table. I am confused whether I separate contactinfo from MyTable or not. ContactInfo ...
1
vote
2answers
80 views

charindex wrong result

Charindex returns no record if only one letter is provided. If parameter matches the whole word it shows the record. Why??? USE [database] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER ...
1
vote
0answers
21 views

DATE - Normalization, a multiple ranged query, multiple inserts and multiple update

I have a booking system, airbnb style. TABLES -> prices, house, available_date A house has available days and not available days, but sometimes the days have different prices. A weekend is more ...
0
votes
1answer
32 views

Data model Reference and schema

I just started to learn MongoDB database and for my project. I am trying to implement a MongoDB reference schema. In my Menu table I would have a food ingredients attributes where it will store the ...
1
vote
0answers
29 views

Understanding Normalisation [closed]

I have a test coming up regarding the 1,2,3 and BCNF Normalisation. The biggest problem understanding I have is from how to convert from 3 -> BCNF. Firstly how to know if the tables are already in 3NF ...
3
votes
2answers
138 views

Is this in 1NF?

I'm an A-level Computer Science student, and my teacher can't explain how to get me from UNF to 1NF. I've researched a little, but I'm not sure as to whether this table (below) is in 1NF or not. I've ...
1
vote
1answer
76 views

Social network DB model in MongoDB

I am building a story site for pre school The basic functions are students will be posting stories and others will be able to like/comment on that story Below is the model of the DB Stories ...
2
votes
2answers
202 views

Relational Design - One table, two foreign keys or Two tables, one foreign key each

Looking for some advice related to optimal design in the following scenario. There is a Cases table (represents cases of inventory) There is a LocationInventory table (represents locations with ...
2
votes
2answers
121 views

Best data modeling approch to handle redundant foreign keys in relational model

I am looking for advice on the best relational modeling approach to store surveys, questions, and responses. I am looking for which of the two approaches below looks best, or an alternative approach ...
0
votes
1answer
104 views

Hi-Level, Beginner Question About Snapchat Database organization

I am a high school student who is interested in developing apps and learning more about databases and servers, so please bear with me if this is the wrong place to ask or if it's a obvious answer. I ...
0
votes
1answer
53 views

multi valued attribute as primary key

I am designing a database system for my current project and got this situation. I am having a functional dependency as D -> E Here D is multi valued attribute. D can be(i,j,k) or (i,j) or any ...
0
votes
1answer
85 views

Derived classes with same base class and class specific ids

I have three classes in my project. One base class and two derived classes. public class Animal { public long Id { get; set; } public DateTime Created { get; set; } public string Name { ...
1
vote
1answer
58 views

Really big LOG backups on a DB with Image field

Normally, our log backups has < 10MB size In a big database with a lot of transactions per hour ). But now we have a database, that is storing Images on a Image field. the problem is, the ...
1
vote
3answers
77 views

Is it a good idea to use a personal number as a primary key for a customer table?

We are designing a new system to handle customers at our firm and me and another developer are split on wether we should use the customers personal numbers as a primary key. My preference would be to ...