The logical model and/or physical design of a database.
0
votes
0answers
8 views
multiple similar rows, where only one field has changed
I have a basic database set up for a school spelling bee (the scenario may seem slightly silly, but I'm using it as an excuse to learn a little more about database design). The main tables contains ...
0
votes
1answer
23 views
Unknown number of potential values in a table from a foreign table
I can't seem to figure out the best way to do this...
Table A Table B
Id Id
TableBId ValueNeeded
TableAId
So I ...
0
votes
0answers
26 views
Designing a simple library database with 5 tables [on hold]
I am currently developing a simple book library system in ms access 2007 and need some help with the database design. Can anyone please help me to make relationships between 5 tables?
Tables are : 1. ...
0
votes
0answers
20 views
should this attribute be added to all entity in oracle data modeler tool?
I'm creating a database for national citizen information. Here I've a super entity "Citizen" and every sub-entity like "Health","Education", "Employment" etc has relation with this "Citizen" entity. "...
0
votes
0answers
26 views
Retrieving aggregated data with filters having billions of rows
I have an ordering system where there are several thousands of records flowing into the system every day. The system currently has 15 million records and adding up every day. It is expected to grow to ...
0
votes
2answers
35 views
How to design parent, child, photos relationships in mysql when photos can belong to either parent or child?
Let's say I have a parent table and a child table with parent_is as foreign key. Now I want to store photos that could either belong to parent or child. so I could have a parent_id and a child_id ...
0
votes
1answer
40 views
Timestamp primary & unique
I need to create a database for data that looks like this:
I am planning to use this table structure:
CREATE TABLE IF NOT EXISTS `AUDCAD` (
`date` BIGINT NOT NULL,
`open` decimal(7,5) DEFAULT ...
-3
votes
0answers
25 views
Any development strategy to make DBAs feel comfortable? [on hold]
As a developer I wonder if there are good or bad advices developing a DB to make it highly maintainable by DBAs? For example (bad practice): if my aplication requests a value of third column - DBA ...
1
vote
1answer
18 views
Integrate Full cast and crew in a Movie Database
I'd like to display full cast and crew from my movie database and was wondering if the structure below is OK. Basically, I want something like this https://www.themoviedb.org/movie/284052-doctor-...
0
votes
0answers
31 views
Database Normalization help for Retail and wholesale Drug store
How can I normalize the following design? Keeping in view my requirements, I came up with the following design:
You can get the sql fiddle here, the fiddle does not shows links between table because ...
1
vote
0answers
20 views
Best way to communicate with ArangoDB [on hold]
Being frustrated with the creative query design of MongoDB, I've recently discovered ArangoDB which looks very promising to me.
As far as I've understood the concept, ArangoDB communicates mainly ...
0
votes
0answers
37 views
(Un-)effective data architecture for accessing versioned data [on hold]
For some time I am part of the IT Department of a medium sized company.
Modeling ETL processes, maintaining databases, Data Warehousing and so on. What makes me wonder is the
data model that is ...
0
votes
0answers
19 views
Could two-phase lock solve the problem of `Unrepeatable Read`?
I don't know whether mysql or other database solve the Unrepeatable Read by using two-phase lock? For Example, in one transaction, add all certain lock at first phase, then unlock them at second phase....
2
votes
0answers
23 views
Optimality of Columns vs. Complementary Table in MySQL
I'm starting work on a project where I'll have a table of entries as well as a table of descriptors.
The entries will be updated infrequently, if ever, and the descriptors table will be largely ...
2
votes
0answers
31 views
How to optimize an ordered date range query with several OR filters (for streaming)?
I have a table of data which is be outputted into a stream, so the upper bound of the cost doesn't matter as much as the lower bound. The table contains about 200M rows. The question is how to ...
0
votes
0answers
13 views
Permissions for access DOM element based on Users Groups
I have been asking for add this new functionality to a system where users can or can't see a DOM element. In other words I should be able from an Admin tool to build what a user can see or can't when ...
0
votes
1answer
16 views
Help with a table having fixed number of self-referencing columns
I have a Road Section that looks like this. Each road section has
Opposite Road Section
Road Section Ahead
Road Section Behind
Visual presentation of Road Section
| | Road |
| ...
0
votes
1answer
37 views
Statistical Database Optimization
My friends and I are building a database for statistical research.
So we have a user that answers 10 questions in a row.
The questions are face-images, one is mirrored and the other one is the ...
0
votes
1answer
12 views
Connecting to a Data Virtualization Tool from an oracle database?
I have this following data of DVT(Vertica)
o DSN Name,Host,Port,Username,Password,Domain,Datasource.
How can I create a DB link using those credentials of a DVT from an oracle database?what query I ...
1
vote
3answers
61 views
Is it acceptable to have a default value on a foreign key
Say we have a tabled called Account that has a foreign key FK_CurencyID, is it acceptable to hard set the default value of the FK_CurrencyID?
For example, if we wanted the default currency of all ...
0
votes
1answer
57 views
Design - Q&A Database - Help
I'm developing an application for my last year/degree at school.
This application is for a mechanical workshop;
It will have two types of users:
Administrator,
User;
Administrator will be just to ...
0
votes
0answers
31 views
MySQL Server memory allocation [closed]
I am working on a Java based real time application. I have dedicated MySQL server(5.7.15) with 2GB RAM and 20GB hard disk.
There will be 1600 incoming data per second, This need to be saved on DB.
I ...
0
votes
1answer
26 views
Storing user search terms
I have php application like airbnb. I want to store search values each time user search for listings.
my number 1 solution would be storing it in mysql table, but I'm not sure its wise to do, if site ...
0
votes
0answers
26 views
Do I store blog posts in a database? And how do I continue to make posts? [closed]
I'm trying to make a simple blog from scratch, but it occurs to me that I probably shouldn't have a bunch of static html pages that I link to, especially since I want the home page to be a cascade of ...
-1
votes
0answers
18 views
Find links between tables of items, which are incomplete and have multiple values for one variable [closed]
I have 6 tables coming from different inventory sources. Each item consist of a unique (Name,code1,code2). However code1 and code2 can be multiple (some have 1, 2, or 3), and they are not always ...
0
votes
0answers
18 views
Designing a DB that will have multiple sites with the same structure [closed]
I am still new to DB design and looking for some guidance on a new site I am building. Users will come to www.mysite.com, they will register their "club" and get a custom management website that is ...
2
votes
2answers
47 views
Marking a row as removed or deleted - is it a good design?
This is a theoretical design question about marking rows as removed/deleted. I assume rows should be marked as removed whenever there is a need to remove row's data, but the row should stay in DB to ...
5
votes
2answers
71 views
How can I represent the entity types depicted in an ERD as relations with attribute headers?
I have created the entity-relationship diagram (ERD) shown below (for brevity, each individual bubble-shaped box portrays multiple attributes):
Considering such ERD, I have to convert each entity ...
0
votes
0answers
39 views
Create one more table or just create a new column in a table
I have 5 tables: person, executive, partner, admin and considering another: Role.
The context of this is, each role (executive, partner, admin) have different permissions accessing some areas, for ...
0
votes
1answer
37 views
data scheme for ordering product with serial number [closed]
I faced with the following issue: I need to design a data tables for storing products. The products has a serial numbers and each one has different attributes:
Now the tricky part is that i have to ...
-1
votes
1answer
36 views
What is the optimal data type for columns that retain dates, DATE or INTEGER? [closed]
In my experience, old school thinking tells that, for optimal performance, date data points should be stored as an INTEGERs (e.g., as in 20160101), since they are manipulated faster, accelerate ...
3
votes
0answers
28 views
When to create a new table if functionality is the same?
I am having trouble making database schema/design decisions. The database I am building is relational.
Consider this example:
I have these tables: orders, products, companies.
I want to create ...
1
vote
1answer
35 views
How to fill in and rename columns in a 2-table join?
I am working on creating a database for machine parts, which will be used by a Java program for further processing.
I have 2 tables, one for the orders and one for the components:
orders-table:
+---...
1
vote
1answer
22 views
Best way to store products of Multiple Fields of different information
I have around 6 table in one of 3rd party cloud database. I am planning to Build it MSSQL. My problem in each of these table there are 20-40 field of different information and Item. Which approach ...
1
vote
2answers
37 views
In which table we should put the foreign key
For example I have a table Admin and a table Role. To do one-to-one relationship between these two tables I can put the foreign key either in the Admin or the Role table. My doubt is if its icual put ...
1
vote
1answer
36 views
Avoiding foreign key repetition on related models in a relational DB
I have a simple DB schema describing certain objects that belong to a historical time period of a country and (optionally) a subdivision of that country at that period. The contrived class diagram is ...
3
votes
1answer
99 views
Multi-tenant database
We are building a database for a SaaS web application. We have been told to use a multi-tenant approach and to expect over 100,000 tenants to be using the system over the next 5 years, so we're ...
1
vote
0answers
20 views
What is the correct way to store multiple values of an entity's property with a “primary” or “default” one?
In previous database model I've had an entity that had a simple name property. Later I'd find out that a single name was no longer sufficient - rarely entities had multiple names and it was time to ...
1
vote
1answer
44 views
Advantages/Disadvantages of using a multicolumn clustering index
What are the advantage/ disadvantages of using a primary key that is a combination of multiple columns?
I have come across a table that has a unique non clustered index as a GUID but the clustering ...
7
votes
2answers
101 views
Flag vs table split
I am designing a table of items which will (potentially) contain tens of millions of records. Some items will not be available for use until they are "approved" by administrator. By "use" I mean that ...
1
vote
1answer
45 views
What table to put my foreign key in?
I have two entity relations called Record_Label and Band.
I currently have:
Record_label = /\ --- band (1:M)
\/
Where the cardinality ratio is 1:M and record_label has a total ...
3
votes
0answers
37 views
ORA-12154: TNS: Could not resolve the connect identifier specified
I am running a script that "creates/replaces" a procedure in a database and then runs the procedure. The code is as below:
WHENEVER SQLERROR EXIT
UNDEFINE g_cust_pwd
UNDEFINE g_db_name
SPOOL '...
0
votes
1answer
20 views
Concatenate fields in access
How come my statement is getting marked wrong? The question is:
Create a query based on the tblCustomer and tblPurchase tables that will concatenate the member’s first and last names (separated by a ...
1
vote
1answer
34 views
MySQL Table Schema Design - Relationship with Junction or User Table
I am trying to determine the best approach to create a relationship between my notifications table and tables with user information within my current db architecture. The purpose of the notifications ...
3
votes
2answers
41 views
How To Add Days And Time For Instructor Schedule
Problem
I need to make a database design (ERD) to display a schedule for every instructor, including the following:
day of course (e.g. Saturday or Monday)
time it will give course (period from 12 ...
1
vote
1answer
18 views
How should I design a re-usable content like table?
So I've got a database that holds articles, recipes, and comments in seperate tables, and I'm wanting to allow users to be able to like each of these content pieces (articles, recipes, comments).
I'm ...
1
vote
1answer
15 views
designing a tracking system for ordered workouts
I've taken a handful of introductory database courses, so I thought I'd design a relational database to help me keep track of my workout routines. It turned out to be a more difficult task than I'd ...
2
votes
1answer
99 views
Data Warehouse Design and Double Dipping
I know this may sound like a homework question, but I assure you it is not.
I am seeking for a second opinion about a lecture, usually I would go and ask the lecturer or tutors, but their answers ...
70
votes
19answers
14k views
Is adding the �?tbl’ prefix to table names really a problem?
I’m watching some Brent Ozar videos (like this one, for instance) and he suggests not prefixing tables with �?tbl’ or �?TBL’.
On the internet I found some blogs saying it adds nothing to documentation,...
3
votes
2answers
71 views
How to re-design my database after an assumption about its structure has changed?
I am designing a MySQL database for working with elevators. The database contains an order which itself contains the technical information about an elevator (screws, ropes, material, ...) and also ...
