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

learn more… | top users | synonyms (4)

0
votes
0answers
34 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
155 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
66 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
71 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
54 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
122 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
85 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 ...
1
vote
1answer
75 views

Designing a database for “not seen” queries

I'm building a large collection of items from which I want to query items that a specific user has not seen before. I will need to track, per user, which items s/he has seen. Currently the items are ...
1
vote
1answer
58 views

What is the best practices for storing sales tax rates?

In Canada we have a federal general services tax (GST) of 5%. Some provinces have a provincial sales tax (PST). Some provinces have a hybridized sales tax (HST) instead of a separate (GST) and (PST). ...
0
votes
1answer
71 views

Not getting current IP address in my oracle database [closed]

I have installed Oracle database in my local machine and my local machine IP address is - 192.168.1.55 but in Toad its showing different SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual; ...
0
votes
1answer
53 views

Preferred database solution for storing sensor data

As a hobby project I am developing a system which stores data from different kind of sensors around the house. Currently it is implemented using ASP.Net MVC 6 and MongoDB for the database. (I choose ...
0
votes
0answers
27 views

unique BIGINT as foreign key instead of primary key in distributed system

I am creating user management database schema. I am using Postgresql as database. Following is my approach. Please suggest if there is any performance issue if I use this structure. Requirement: ...
0
votes
1answer
55 views

Does exist some software for transforming data from one DB schema to another?

I need to transform between two different database schemas the same data. For example i have one table in old schema like this: and i need to transform it to new schema like this: So i have few ...
0
votes
0answers
20 views

What is the correct database design for accommodating a request and a receive transactions?

In my project management system, I have a feature that requests a rental from a supplier. Once the set of equipment arrives, I can now create a rental receive to monitor the equipment inventory which ...
3
votes
1answer
24 views

Events-Users Database Design

I am creating a web interface for upcoming events in a community. The users can create an event, join an event, and also leave an event. The interface will list the upcoming events with the following ...
1
vote
1answer
23 views

Check if two databases have same structure [duplicate]

I have a postgres database that was developed during time, adding tables and keys when needed. Now I'm using a tool (Enterprise Architect) to document it. The tool allows to create automatically the ...
0
votes
0answers
17 views

Is it possible to have a 2 tables that has a foreign key with each other? [duplicate]

I mean, table1 has a foreign key reference in table2 and vice versa. I am asking this because we can't drop a table that is referenced by another table with a foreign key constraint. If it is ...
1
vote
2answers
54 views

Better deffining a parent-child relationship on a table

Our team is developing a new system and it has a Warehouse table that must have a hierarchical relationship between entries since a customer can have 1st, 2nd, 3rd and Nth degree warehouses. We first ...
14
votes
4answers
1k views

Storing bus routes in a database

I have done some research and found that I should store a route as a sequence of stops. Something like: Start -> Stop A -> Stop B -> Stop C -> End I have created three tables: ...
0
votes
1answer
21 views

Database schema for timesheet app?

I am very new to database managements as I am enrolled in my first class involving databases at university. For my project, I have to create an app that clocks employees in an out and keeps track of ...