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

learn more… | top users | synonyms (4)

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
72 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
57 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
24 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 ...
2
votes
1answer
49 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
16 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
30 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
86 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
30 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
20 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
49 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
50 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 ...
0
votes
1answer
38 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
82 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
47 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
41 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
83 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 ...
1
vote
3answers
112 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
113 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
47 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
62 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
71 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
77 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
34 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
52 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 ...
1
vote
1answer
37 views

Dedicated tables VS Multipurpose Tables

I am developing a forum like app using a MySQL database. I have posts, comments and replies just any other forum. At the moment I have a table for posts, a table for comments/replies and a table for ...
0
votes
0answers
33 views

Replication vs extracting just the data we need?

I recently did a project with a developer who wrote a desktop app. This is used by a few businesses, and each business has their own database installed locally. The app does not use any Internet ...
0
votes
1answer
25 views

Setup the user for edit/view permission to assigned one assigned entity of three (tables) available [closed]

I have three entities of store, business, and corporation. User can edit/view/etc (permission) of entities they are assigned to. User can only be assigned to one to the related entities. The above ...
2
votes
1answer
68 views

Quiz database design

I'm currently working on a quizsystem and are trying to make down the database design at first. I've read some threads about different quiz but hey are all quite unique on some parts. The system ...
1
vote
0answers
56 views

Database design in MySQL workbench

After doing some change As Mr Vérace told me here in this question, by changing the id names of each table into table_name_id and using the singular notation, I still need your opinion about this ...
2
votes
1answer
56 views

How to model the relationship between these three tables

This is my first database, it is for a nutritional info meal tracker program. I have created a model in mySql work bench but I'm not sure if I have the relationships right. See image below.. My ...
2
votes
0answers
50 views

Should I move repeating foreign keys into separate table?

Idea Users can upload lots of items and send them from processing. From the upload settings I can set a channel to which the uploaded items belong to. Channel is a separate entity. Currently I set ...
1
vote
0answers
36 views

ER Diagram - Choose One or Both of the Entities

So I am attempting to create an ER Digram for a certain catering service. Two of the entities are "Lunch Menu" and "Dinner Menu". Each catering reservation (entity) must contain either one of the two, ...
0
votes
1answer
30 views

Array or Object?

I am using MongoDB and designing a database. There is a list of itemCode. It won't change, but some new one will be added at some point. The total number of itemCode will never be over 100. Each ...
0
votes
1answer
27 views

Unique values depending on context

So i am implementing a system that has PIN (Personal identification number) codes. The PIN is a simple combination of a few letters and some numbers. The PIN could be something like AX7500. Now, a ...
1
vote
1answer
49 views

Extra fields in user_role / roles tables or a new table

I have a user_roles table which I use to manage permissions and user's access to certain things. For example, a team manager is a role. Since a team manager belongs to a specific team, the way I ...
1
vote
1answer
93 views

What to name a field that includes Store and Version ID

I am building a database that manages the definitions for a POS system (menu items, cashiers etc). It will include many stores and many versions. So, typical table will look like this (MenuItems ...
3
votes
1answer
75 views

Optimal design for a large database

Brand new to databases, using postgresql on linux. I've done a decent amount of reading on the issue and looked through similar questions on here, but just want to confirm the most efficient way of ...
0
votes
0answers
45 views

Review of my database

I had create a simple database for e-commerce application. I want somebody to review my database. Note:I'm making this for educational purposes. Tables: +----------------------+ | ...
3
votes
0answers
76 views

Is this user-storage database system efficient, effective and secure?

I am using: PHP Version 5.2.17-MIT-BACKPORT-GnuTLS SQLite 2.8.17 I have a database called users, which currently has a table called root. There will be others, once I get all the rows for root ...
1
vote
1answer
52 views

Normalizing data for joins, dedicated columns, a view or.. something else? [closed]

As an example, i have the following tables USERS, USER_JOBS and USER_HOUSES USER_JOBS keeps a list of jobs for a given user_id USER_HOUSES keeps a list of houses for a given user_id the common use ...