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

learn more… | top users | synonyms (4)

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
56 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
41 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
34 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
27 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
89 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
66 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
64 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
54 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
39 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
33 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
28 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
66 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
102 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
78 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
46 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: +----------------------+ | Tables_in_e-...
3
votes
0answers
79 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
46 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
43 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 I'...
4
votes
2answers
80 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
44 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
55 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
21 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
62 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
69 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
47 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
42 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
54 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 ...
3
votes
2answers
69 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
63 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
521 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
20 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
37 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
103 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
58 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 ...
3
votes
1answer
92 views

Most efficient way of caching queries / processed data

I have doubts on database design. I'm trying to know if there is a way to increase query performance, and the easiest way for me to explain my doubt is with a small example. I have a news website ...
3
votes
2answers
59 views

How to model A Shop Database

I want to draw the ER model of a store which The scenario is like this: They have list of Goods and Some Seller which sells them to customers they want to know how much they get profit from their ...
1
vote
1answer
45 views

Database structures for similar profiles

Please bare me if my question is too blunt I am working on a social network where I need to show similar profiles to users Every profiles has 40 parameters to match with other profiles. I want to ...
1
vote
0answers
73 views

Storing and searching user location using latest MySQL 5.7 spatial features

I want to use MySQL spatial features to store user location. I have read about MySQL spatial geometry data type. According to this article this works very well in MySQL 5.7 http://mysqlserverteam.com/...
6
votes
2answers
219 views

Efficient Database schema for multi-lingual content?

Assume I have a table for blog posts, posts, and schema as id author title content and each post can belonged to some categories id name descrtiption So a blog post can belong to multiple ...
1
vote
0answers
69 views

Creating relations using tags

I have three tables: Product, Accessory and m:n relation between Product and Accessory. There are currently around 200 products and around 600 accessories. The user wants to have tags on the ...
2
votes
3answers
89 views

Integer ID vs varchar ID with JOIN

I have a table of users but I don't know what is the best practice between using a unique MEDIUMINT identifier or a unique pseudonyme identifier using VARCHAR. With MEDIUMINT identifiers, like the ...
1
vote
1answer
39 views

Two tables with same field

QUESTION: I have QUOTATION table and SOA table quotation - table is for pending and not approve quotation. SOA- table for approve quotation. i will just transfer the data from quotation table to soa ...
0
votes
1answer
45 views

MySQL - design help for scale

I will be using Amazon Web Services RDS service to host a MySQL 5.7 server (Currently AWS do not support 5.7, but they have announced they are working on implementing this version and it will be out ...
0
votes
2answers
104 views

Database Design - Voting System

I'm creating a website in which people can submit there own lists, and then essentially others Up/Down Vote the items within the list. I'd like the list (If the submitter allows "votes") to be ordered ...
0
votes
1answer
40 views

Relationship between data independence and database schema

What is the relationship between data independence and database schema? I know both of them separately. But I don't know how to explain the relationship of these two.
3
votes
1answer
44 views

Explaining the problem with a column of values from a non-mutually exclusive domain

I'm trying to convince the powers-that-be that their current database design is risky and inefficient. I'd like to verify whether my assumptions are correct, and I'd like help in finding technical ...
0
votes
1answer
20 views

Design - duplicating properties vs not allowing delete

I'm designing a database for a mobile app CMS that contains user data. These objects are Address and Job (a job can have 1 address and an address can have many jobs). The first version of a similar ...
1
vote
0answers
20 views

Question on comparing two fields in Access

I'm not sure if the title is fitting as I am not 100% sure if what I am doing is correct but here goes. Here is my enrolment table, I have added a field called pass/fail as you can see which is ...