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

learn more… | top users | synonyms (4)

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
48 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
39 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
80 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
109 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
109 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
60 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
35 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
53 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
29 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
46 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
92 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
74 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
44 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
74 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 ...
0
votes
1answer
33 views

Array as a column vs lookup table

Hi I have two tables that has one to many relationship. Table 1 Users: ------------- | id | name | ------------- | 01 | John | ------------- | 02 | Harry | ...
1
vote
1answer
42 views

Constraining inserts/updates for summary tables with complex validation logic

I am designing some MySQL tables and am running into an issue that I believe can be solved by a uniqueness constraint, but not sure exactly how. My actual use case is complicated and confusing, so ...
4
votes
2answers
55 views

Postgresql inheritance based database design

I'm developing a simple babysitter application that has 2 types of users: a 'Parent' and the 'Babysitter'. I'm using postgresql as my database but I'm having trouble working out my database design. ...
0
votes
0answers
33 views

Question on Normalization and ERD diagrams

I am doing an assignment where I normalized an invoice. my 3NF looks as follows. PK invoice number: order number invoice date invoice credit card invoice shipping invoice tax ship to ...
0
votes
2answers
52 views

Does a record with NULL data does not eat any storage on the harddrive?

This is not an expert question about database, so I'm not sure if this question can be posted here or not. So if this question is out of place, feel free to close it. It's a long development story, ...
1
vote
0answers
20 views

Help needed designing a 'Group' concept for students in a class

I've been wracking my brain for a while now trying to figure out how to fit this part of my domain model into the database, but whatever I come up with just feels wrong. I've attached a picture below ...
0
votes
2answers
56 views

Normalisation to 3NF

I've been learning how to normalise data into a normalisation table and I'm struggling to get my head around this example question. I have this 'clinical record' card: Would anyone able to explain ...
0
votes
1answer
57 views

Design a website database structure

I am really stuck on the database structure of the shopping cart website. I am trying to create a sample shopping project for learning purpose where I am getting heavily stucked. Here is my sample ...
0
votes
2answers
41 views

Modeling a product database and dealing with inheritance

So, to get better at designing databases, I'm attempting to model a database system after the AR15 Builder. Here is what I have so far: While it makes sense logically, I'm afraid there may be ...
0
votes
0answers
39 views

Database design for flights, airlines and aviators

I created two tables Airline(airline_id, name) Flight (flight_id, airline_id) - airline_id contains id of airline which perform flight and I want to create third one Aviator But I can not ...
3
votes
1answer
47 views

Database design, 1-to-many or many-to-many?

Just a little background information pertaining to the question at hand: I'm developing an application for users to be able to log the work they have been doing hourly, to be compiled into a weekly ...
2
votes
2answers
59 views

3rd Party Platform Unique Identifier for Joins vs Internal Identifiers

What is the general practice when it comes to choosing a unique identifier? In my case I have the option to use the eBay unique listing ID (Item ID) or my own internal ID for use in table joins and ...
4
votes
2answers
62 views

Does my m2n-table need a primary key?

Two tables called Chip and Dale have m2n-relations to each other. I am used to build a link table called CHIP2DALE (or Dale2Chip) containing id NUMBER UNIQUE NOT NULL chip_id NUMBER NOT NULL dale_id ...
5
votes
2answers
470 views

Likes or votes for posts

I am making a small program where users makes posts or write blogs. On those posts, other users can like or dislike the post as in facebook or upvote or downvote the post as in stackoverflow. I would ...
1
vote
1answer
19 views

Advice on Measures that have properties on their own

I'm looking for advice on how to model a database when the measures have an increasing number of properties - the values of which could be considered dimensions in their own right. In a simplified ...
0
votes
1answer
35 views

Internal Online Store Database Design Issue

I am trying to create a database for an internal store that we are developing to manage our internal items (like phones, computers, office equipment, etc). The issue Ive run into is that they want ...
2
votes
1answer
93 views

Is it “OK” to have a table that only contain foreign keys?

In "OK", I mean "it doesn't have any bad impact with the overall database design". For example, will it cause insertion problems? or will it be a problem in the future when I add field in the ...
3
votes
1answer
46 views

Database design for products with bundles of products

I am building a database system for my retail business. I have set some tables which are: Product Purchase Sales Balance All are connected one another and are able to show my inventory level. The ...