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

learn more… | top users | synonyms (4)

0
votes
0answers
28 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
36 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
51 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
107 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
83 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
52 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
66 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
83 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
42 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
99 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
15 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
132 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
84 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
89 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
46 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
107 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
81 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
81 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
92 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
75 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
31 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
64 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
23 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
31 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
32 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 possible,...
1
vote
2answers
68 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: Routes ...
0
votes
1answer
25 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 ...
2
votes
1answer
52 views

Definition of approach to store temporarily unknown data

What structure would be more reasonable to store temporarily unknown data? Initially we do not know some aspects of the data we are working with, but such aspects will be filled in at some point. ...
0
votes
0answers
17 views

Category of the same object in SQL and Inheritance.

I am trying to make the relational schema for a shop which sells ice creams and drinks. As the ice creams and drink both fall in the same category of Items, i tried making the items entity but i am ...
2
votes
1answer
44 views

Should future time be stored in UTC or local time?

Clearly in most cases time should be store in UTC. And in all cases a datetime that records a past event should be stored in UTC. We do this because civil time keeps changing and an event happened at ...
0
votes
1answer
88 views

Encapsulate table with view

I need to change the order of columns in my table (Oracle and SQL Server). Reason is that some of our software calls select * and insert without specifying columns. Refactoring will be too expensive ...
0
votes
1answer
38 views

To use tpc-h benchmark its really necessary create primary and foreign keys?

Im doing some tests with tpc-h benchmark. And Im with a doubt, and I didnt find any answer. The tpch-h benchmark simulates a data warehouse environment. And the most important here are the queries ...
0
votes
0answers
22 views

Table design one or more

I am struggeling with the following issue. I have a table executives and supervisor, quite similar in the structure. Both of the executives can have a Car one or more. Should I make a table ...
0
votes
2answers
109 views

How to store historical records?

What's the best practice approach to storing historical records? I have an entity whose rows will constantly get updated. I need to keep track of each row's history. There will be about 40K "current" ...
0
votes
2answers
51 views

Practical Aspects of Making Changes to Databases [closed]

I am setting up my first database and have gone through the standard process of studying the business logic, figuring out the entities, associated fields and dependencies. This is the theoretical/...
0
votes
1answer
44 views

What kind of table structure to use to display product, testing, and test results?

I am creating a database, but I am a beginner. We test several different kinds of paint, with specific test methods, and I would like to document all of the results. So for an example, I would test ...
2
votes
1answer
88 views

How to set up foreign key constraints referencing more than one table

I have a simple structure where I am not sure how to handle some of the issues that pop up. There are 4 tables: Reports, ReportParameters, ScheduledReports, ScheduledReportParameters. ...
3
votes
1answer
52 views

Use large set of data in multiple databases

I have been thinking about this a lot but somehow can't figure out how to deal with it in an efficient matter. Say you have a large set "X" of data that consists of multiple tables. For the sake of ...
0
votes
1answer
49 views

MySQL ADD KEY meaning

This code below is creating a primary key on table User, but Im not understanding the second ADD KEY (C_NATIONKEY), do you know what is doing? Its something about foreign keys? ALTER TABLE USER ADD ...
3
votes
0answers
90 views

Best design to reference multiple tables from single column?

Proposed schema First and foremost, here is an example of my proposed schema to reference throughout my post: Clothes ---------- ClothesID (PK) INT NOT NULL Name VARCHAR(50) NOT NULL Color VARCHAR(...
1
vote
3answers
145 views

How to draw ER diagram for recursive relation when the number of roles is more than 3?

I know for a recursive relation where there are 2 roles, for example: Worker works for Manager and Worker and Manager both are 2 roles of Employee, we show it this way: But what if Employee had ...
0
votes
1answer
200 views

Huge database… queries too slow. How to improve performance? [closed]

I'm currently managing a MySQL database with 30 million rows(many more to come). The entire database is 280GB. The server has 4GB of RAM and 3.2Ghz 4 core processor. The largest column stores an array ...
1
vote
1answer
64 views

Best way to create searchable and sortable product database

I'm pretty good with coding but I'm a noob at database design. This is a common problem but I didn't see an exact answer so I just want to confirm before I start coding everything. I have a shopping ...
0
votes
1answer
226 views

ORA-29913 and ORA-30653 error selecting an external table

I created an external table using a .tbl file and now Im trying to select all rows from that table, using: select * from users_load; But Im getting this error: ORA-29913: error in executing ...
2
votes
1answer
75 views

Alternate Name of Data Dictionary

What is the alternate name of Data Dictionary? ► Index ► Metadata ► Data ► System Catalog What I know already is, data dictionaries store all the various schema and file specifications and their ...
1
vote
0answers
84 views

How to structure Many to Many relationship

I have a customers tables, the customer can be either a company or individual. If the customer is a company, then it has specific structure, that means that each company has a (Director , secretary, ...
1
vote
1answer
39 views

Check constraint to require one of two columns

I'm using SQL Server 2008 R2 and would like to make one of two columns required on a table; in other words, if the user provides a value for one, the other isn't required but the user can provide a ...
2
votes
1answer
60 views

stored procedure can select and update tables in other databases - minimal permissions granted

I recently had to make the stored procedure spPartyOrderAllocation_AllocateItems to select and update tables in other databases, while keeping the pemissions levels to the minimum. This was ...