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

learn more… | top users | synonyms (4)

1
vote
3answers
97 views

Mysql - How number of columns effect the database performance

I have a table to store social media information for users; I have 42 columns to store and will have 100000 entries per day to store. I am planning to have MyIsam engine for it and will use horizontal ...
0
votes
0answers
15 views

What is a fast way to calculate counts and sums?

I have the following data: A journey which contains this info: - a geohash (to identify the location) - a journey_id - whether this was the start or end of the journey - the cost of the journey I ...
1
vote
1answer
25 views

Does the existence of a college course depend on the department in which it is studied?

I have a Department entity along with a Course entity, does the course entity depend on the department or is it a strong entity by itself since it has an ID that can distinguish it from other ...
0
votes
0answers
27 views

Does an exam entity in a student information system depend on the department or the student?

I have made an ER Diagram for a student information system where I have the following entities: Student Department Course Administrator Exam Attendance I'm having trouble determining whether Exam ...
0
votes
1answer
34 views

Which isolation level do I need?

I've read a lot of articles about Isolation levels, but I'm confused which one to choose for my MySQL 5.6 database. This is my situation: Let's say I have two group of users: Group A ...
0
votes
0answers
34 views

Registration Schema Implementation

I'm building an online app that is going to handle reservations. Individuals making reservations can make one for a single day or multiple days. I have come up with two schema and cannot see much of a ...
0
votes
0answers
20 views

Address database scheme - multiple links

I am currently building a databse scheme for a small company. But at the point doing it I figured out that my attept will be unacceptable due to the ability that one action will affect multiple ...
3
votes
2answers
84 views

One-to-many to multiple tables

What's the normal way to design a relationship where every entry in table A is connected(1:n) to either an entry in table B or table C? Or specifically: There are a tables for characters, movies, ...
0
votes
1answer
30 views

How to store Alternate Names for Places or City in Table

What is best way to store Alternate Names of the Places (City or Locality) in MySQL database columns which user will be searching? Examples: In India user may search for "Chennai" or "Madras" and ...
2
votes
0answers
39 views

insert/update from web server or database

I have a data-model on the front-end that I want to insert/update in the database which of the following approaches (or neither!) is considered best practice: # in webserver some_model = {json} ...
3
votes
1answer
71 views

Database design decision help

I currently have a database design that I have been looking at and I am wondering if its the best one. I have the following tables: Each request has a current status. I need to keep track of all ...
0
votes
0answers
23 views

Database design for efficient read queries given lots of writes

I have a PostgreSQL database, and an application which receives around 200 requests per second. Each of those requests contains a message with an identifier representing a journey id, a timestamp, ...
1
vote
1answer
73 views

How to efficiently design MongoDB database for Uber-like application?

I'm designing a MongoDB database for an Uber-like application, but since I'm new to NoSQL concept, I have several doubts. I have three main collections - users, requests and messages. User can post a ...
0
votes
0answers
37 views

How could I improve speed of fetch in Oracle?

I've got a table of taxi GPS trajectory records with approx 60 million rows, including date, time, ID, longitude, latitude, speed, bearing etc., which were imported from .csv files with setting each ...
3
votes
1answer
48 views

which way is better to design shopping Cart Table - SQL Server

I want to create a shopping cart in an online store. I have two ways to design shopping cart, but I don't know which one is better. Solution 1: create a table like below : in this way for each ...
0
votes
0answers
29 views

How to store general information about an entity

I am designing a database to store data relating to management of a school. I have tables representing the sorts of things you would imagine: Students Carers Invoices and so on. One feature I wish ...
2
votes
1answer
70 views

Is it bad practice to use separate columns for parts of date values?

I recently posted these questions in order to try and improve the performance for my queries. DBA Q&A 1 DBA Q&A 2 One of the suggestions was that I should use a DATE() column rather than ...
21
votes
8answers
2k views

What problems are solved by splitting street addresses into individual columns?

We have a team who designs the tables and relations for software developers. In our organization, they are pretty strict about enforcing 3NF normalization - which to be honest, I agree with given the ...
3
votes
0answers
49 views

Ranking items on a per user basis

I have a MySQL DB with 20k-50k items and I want various users (let's say 1k-10k) to rank these items for themselves, so this is not going to be a global ranking but a per user one. All items are ...
3
votes
2answers
79 views

Database model about libraries

I need to make database model about libraries. In that model I'm tracking more libraries and every library has it's own moderators. Every moderator can make categories of the books and than add books ...
0
votes
1answer
27 views

Best way to apply a “friend system”-like table? [duplicate]

I am making a website that has multiplayer games like chess, poker, etc. Users will be able to add friends. I was thinking about how could I make this in a DB, and I thought of two methods: 1 - ...
5
votes
2answers
160 views

Is it possible for a weak entity, already related to a strong entity, to get related to another weak entity in an ER diagram?

I have a problem statement (briefed), Database for a Fitness Club - Client's Attributes: Name, Address, Ph. No. Membership Plan's Attributes: Beg. date, Sub. Fee, Monthly Fee, Duration ...
1
vote
1answer
28 views

Is row based or column based raw input better?

I have a vendor that is starting to give me some rather large reports on course completions and usage. They have two formats for the reports: If a student is enrolled in a course, they will have an ...
-1
votes
1answer
68 views

how can I add multiple values in a single column?

I'm working on a project and I have a scenario We have a vendors table, some vendors have many contact no. how can i add multiple phone numbers on a single contact no. column?
1
vote
0answers
19 views

Efficient Provision of Multiple Versions of Data

We have a fairly unusual database use case: we have a number of large datasets used for research (the largest being a couple of billion rows). The datasets are used by potentially several hundred ...
0
votes
2answers
44 views

One connection vs multi connection

I'm redesigning my client's website in order to add about 16 mini sites for their web site. It's a website using PostgreSQL. And my question is: Performance wise, what is the better database design: ...
1
vote
0answers
34 views

Handling Strong and Weak entity types in a one-to-one relationship

I am modelling a database for a term project and I have come across something I do not have a definite answer to. Diagram I was supplied with the following Entity-Relationship diagram: ...
1
vote
1answer
38 views

How to store user activity that supports undo?

I can track user activity on posts, so that users can see for example "2 updates!". The essence of the strategy I’m using leverages 2 tables: post_activity: post_id | user_id | time_created ...
3
votes
0answers
33 views

SQL Server storing predefined value sets along with open values

I have Cars with different Characteristics, Characteristics have values, which can be restricted by predefined set of values or be "open" values (any string). Characteristics and predefined values ...
1
vote
0answers
25 views

creation of stack exchange as a project [closed]

I have been given a project to create a database for stack exchange. should I use a single database for all the sites or I should create a separate database for each site and link them
0
votes
1answer
26 views

How to show the Join with Crows Foot Notation

I could be barking up the wrong tree, but I'm trying to illustrate not only what the relationship is between entities, example, Customers shop in many stores, but also how to join the two entities. ...
1
vote
1answer
49 views

Database design for ordering complex products

I'm working on a platform in which you can order food from different sellers. Currently the platform is working and restaurants can create food products while clients can order them. Easy enough. A ...
0
votes
1answer
45 views

Should a TeamMembers table have a relationship with the Student Table or the Enrolment Table?

I'll keep it nice and short and simply link to the ERD: Users can be assigned a role, one of those is student. So for the sake of this, Students are the same as Users! A Student can be enrolled in ...
0
votes
1answer
35 views

Question regarding database design

I want to store data of customers and employees. What I am having trouble deciding is what is the correct thing to do: a. Option a: Two tables - Customer and Employee b. Option b: Three tables - ...
0
votes
0answers
25 views

Microsoft Access Form's Combo Boxes Help

I have a database in access and I am creating a form to enter transactions for investors in our hedge fund. The form is supposed to create new records in the table tblTransactionsAccrued. I created a ...
1
vote
1answer
35 views

Database Design help

I am building an application for comparing two or more songs. We have millions of songs. Currently we have access to raw data files (form a company like soundcloud) consisting of 1) Daily Users.csv ...
1
vote
1answer
49 views

SQL query returning cartesian and duplicates and not single records

So I have four tables as follows: CREATE TABLE softdrinks ( drinkID INT NOT NULL AUTO_INCREMENT, drinkName VARCHAR(100), PRIMARY KEY (drinkID) ); CREATE TABLE categories ( categoryID INT ...
4
votes
1answer
97 views

Foreign key with additional constraints?

There is a table called Item(id, name, cost) and Orders(id, bill_id, item_id, units), which is created to track orders placed, where same bill_id means it belongs to a single order. How to impose an ...
0
votes
2answers
73 views

Storing multiple types of users and their respective credentials in separate or same table?

So there have been many questions on Stack Exchange DBA and Stack Overflow that go close to answering this, but there's still some dispute within my team on how to properly do a database design. In ...
2
votes
1answer
40 views

Best Practice : Schema design for Large Number of rows with few columns

I have about a data set that each entry is about 750,000 rows under it with each row having four fields. Each entry is in its' own CSV file currently and I want to get it into a database. Currently I ...
0
votes
1answer
55 views

contradiction of 3rd normal form on address table

I have an Address table and its columns are address_id, address_1, address_2, city, state, country, pin code .Its primary key is address_id . According to 3rd normal form no non-key attribute should ...
2
votes
1answer
63 views

Database design — companies, employees, individuals

I'm having troubles modeling this database schema, will appreciate any help. Here is the requirements and description. A user on registration creates his profile. User can be an individual or a ...
0
votes
0answers
38 views

primary and foreign keys used in object-relational table

Currently I am designing an object-relational database based on an existing relational database with multiple primary and foreign keys, and was wondering if it is OK to use primary and foreign keys in ...
2
votes
1answer
95 views

Lost (and found?) on 4NF

I'm so extremely lost on 4NF it's not even funny. I've been reading on it for literally days and, except for about 15 minutes ago, I wasn't sure that I had even broken the surface. I think I might ...
0
votes
0answers
14 views

How to set a Totals column based off sum of another table

I am currently developing a web app with the following tables: QuoteHeader Id etc... QuoteLineItem Id QuoteHeaderId TotalPrice etc... I'd like to have a column in QuoteHeader that shows the ...
3
votes
4answers
123 views

Should I Denormalize Tables To Avoid Joins?

I'm in the process of developing a web application, and need some help with some basic db design. Currently, I am unsure of the following three tables: ### User ### - Id - DetailId - etc... ...
1
vote
0answers
48 views

Postgresql string array vs join for user management schema?

I am creating a database schema for user management in PostgreSQL. As, it supports array, so I am trying with three approaches. Which one is best, querying with joins or getting/setting role data ...
-1
votes
1answer
61 views

What is the best practice for storing User information?

I have a web application to be used in a college or university for application of courses online. My problem however is which practice I should use to store the user information. The users for this ...
1
vote
0answers
40 views

Database table(s) to record investor activity

I've never asked a question here before. I am currently designing my first database by learning from the web and some books I got, using MS Access to start. I work in operations for a small hedge ...
3
votes
2answers
86 views

Modeling a Car Dealership context in an Entity-Relationship diagram

I am creating a conceptual database model for a car dealership. I have spoken to the client, gathered all the requirements, and thought I had my Entity-Relationship diagram all correct; however, I ...