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

learn more… | top users | synonyms (4)

0
votes
0answers
22 views

Help creating database schema to build database off

not sure if this is the right area for a question such as this but I am looking to create a database for an IS warehouse who deliver laptops, desktops, monitors and other peripherals to different ...
0
votes
0answers
22 views

When is it suitable to turn a table around [duplicate]

When I create tables to store data in, I have columns for each property of the model to store. IMHO this type of table design is easy to read and logical. Example below: uid | firstname | lastname | ...
4
votes
2answers
80 views

Where/how do I save extra data per column?

Next to the actual data I have in a table, I need to save some data about those fields. I'm hesitant to call it metadata, because while it is exactly that, metadata has a specific meaning within ...
1
vote
0answers
20 views

How to bucketize using Query optimal bucketization algorithm

I am trying to implement query optimal bucketization algorithm (QOB). The input of the algorithm is D=(V,F) where V is the value of the attribute and F is the frequency of occurrence. For EXAMPLE D ...
0
votes
1answer
36 views

Generate incrementing IDs without sequences

Suppose there's a multi-tenant application where users can create some kind of documents with basic structure like CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT); CREATE TABLE documents ( ...
3
votes
1answer
40 views

Seeking feedback on table relationships, general schema design

I'm designing a database for a business that sells tickets to a variety of events worldwide. The app will be built with Rails using PostgreSQL. Here's what I have so far, link to full size image ...
5
votes
2answers
463 views

Relation between first name and last name

I have some troubles understanding the relation between the a person's First name and Last name in a table. (In a Relational Model) I've read that the relation is of type: 1-1: They are linked ...
0
votes
1answer
23 views

Hibernate modelling a N:N Relationship

Lets say that we have two entities Entities: Bus (idBus*) Stop (idStop*,stopDescription) Where a Bus passes from many Stops and vice versa, therefore the cardinality is N:N. My ...
1
vote
0answers
40 views

Database for buses timetables

I have to design a database for buses timetables. Entities: Bus (idBus*) Stop (idStop*,stopDescription) Line (idLine*,lineDescription) Position (lat,lon) Some constraints are ...
1
vote
1answer
22 views

Google Datastore model User and Messages efficently

I have two kinds: USER and MESSAGES. A user can send/receive many messages to/from another user. Thinking in a relational way the relationship would suggest a user can have many messages, and a ...
1
vote
1answer
38 views

Apply Definition of MVD (multivalued dependency) to Example?

Supposedly this multivalued dependency (MVD) and this functional dependency (FD): {W} ↠ {N,D} and {W} → {N} hold in this table: V W N D ================= | v | w | n | 1 | ...
3
votes
1answer
37 views

Normalised database with one-to-one relationship

Is it bad practice to use a 1-1 relationship between two logically distinct tables: the user details and the user configuration settings? Each configuration record would have a single unique user ID, ...
0
votes
1answer
64 views

Schema design for products with multiple variants/attributes?

I'm using MySQL. The idea is similar to shopify with a different concept, so users are going to add their own products with multiple types of variants and attributes. From all the research I've done ...
2
votes
1answer
25 views

Turn off auto qualification of table names when creating a view

Problem Definition I am trying to modified a shared development database, for a dev team to use for application development/testing. Most of the collective work (table names, views, etc) are stored ...
1
vote
0answers
18 views

Is there a design model for tracking processes done to an item?

I am working on designing a database model that involves tracking processes almost like an assembly line. There is only one type of item (lets call it 'unit') that has about 10 different tasks done to ...
2
votes
1answer
47 views

Adding foreign key that references a column which is not Primary or Unique key

I got into trouble when I want to limit values of Table A's column to be taken only from a set of optional values in Table B's relevant column. Problem is table B's relevant column isn't a foreign ...
0
votes
0answers
50 views

Database schema for storing edits in the content [duplicate]

I want to store all the edits of a specific table. Multiple fields can be modified and for different users. I want to store all changes, taking in count that the number of fields that can be edited ...
1
vote
2answers
54 views

need better db design for project

I have two tables: notes users Notes Table: id || description ||CreatedBy(which is userid)||......other columns --------------------------------------- Users table: id || name || ......other ...
0
votes
1answer
78 views

MySQLSyntaxErrorException: You have an error in your SQL syntax;

This is the issue I am trying to fix since hours, almost a day. I am unable to get an understanding of what might be wrong. I have a SQL table notification I am trying to persist a java object ...
1
vote
2answers
45 views

Sql tables design

I have a table with two columns: Brand(varchar) | optional_os_type(varchar) for example: +--------------+ | Lenovo | 1 | | Dell | 2 | +--------------+ Whereas 1 = win8.1 2 = win10 Currently ...
0
votes
2answers
40 views

Optimal database structure for fast inserts

I am using MySQL Percona server. This table needs to do fast bulk inserts using 'INSERT INTO ... VALUES' for smaller requests (about 100k records) and using 'LOAD DATA INFILE' for larger (up to 10kk ...
3
votes
1answer
133 views

IS-A relationship with only single sub entity

Hi I'm trying to design a database for a dictionary. The words has several fields like meaning, an example, and maybe a picture. but the words that are 'verbs' has three more special fields that the ...
0
votes
0answers
23 views

ER and Precedence Diagrams

I've been having some difficulties creating a "Cruise Ship" database. Basically, what is known to me: Office has information about: Route (final destination/target) Ships Room Room's class (rank) ...
0
votes
2answers
54 views

standard solution for storing units of measure

Let us say I have a table that stores height of a person, something like this : PersonTable <ID, Name, Height> Height is measured in centimeters. My question is about storing unit of ...
1
vote
1answer
26 views

Collection of different types of data in a relational database

Let's say I have a table Cars (with columns such as model, make, year, etc.), a table Books (with columns such as name, author, ISBN, etc.) and a table Wines (with columns such as name, appellation, ...
0
votes
0answers
23 views

Data Model for a popular/basic type of social app

I am trying to learn about Google Datastore and creating a basic application to have some practical experience with it opposed to just reading the docs. I would like to create a basic social ...
5
votes
2answers
163 views

Dog Adoption Database Design

Apologies if this isn't the norm, but I am seeking advice for the database design for 'dog rescue/adoption'. I've done my normalization research and this is my first attempt at database design. Any ...
0
votes
0answers
62 views

“mysqld got signal 11” error

I have a MySQL 5.6.22 InnoDB database and just now, suddenly out of nowhere, when I try to start it up using /etc/init.d/mysqld start, I see this error in the error log and the server won't start up : ...
-2
votes
2answers
79 views

Random numeric string as a primary key - security, performance, efficiency?

I'm building an app aimed to store lots of records (millions) and trying to find a best way to store records identifiers that is publicly accessible from the web (record URL). I'd like to get: ...
1
vote
1answer
41 views

ERD modeling organization and roles

I am trying to model the following scenario: -I have multiple company bureaus. -Each Bureau has multiple offices -Each office has multiple divisions -Each Bureau has employees that only ...
2
votes
3answers
51 views

What is the best practice for treating fields with only two possible values?

A column/field will only have 2 possible values, either "Transplanted" or "Direct-seeding". Is it better to create a table for these options and give them unique IDs or are they better off put as raw ...
0
votes
0answers
20 views

Do I need to create models if im not going todo anything with that data

I am building a online golf team entry form. I am getting confused as to what models I really need. Basically there is a few leagues and zones(divisions) and in each zone there are 8 fixtures and at ...
0
votes
0answers
33 views

How do I design a database for a registration system?

The concept is something like this: Organiser creates event. User registers for events. Events can be for multiple days. One event can have different competitions. User can enter into any ...
2
votes
2answers
103 views

Does a composite primary key break the first normal form?

Does having a composite/compound key (using multiple columns/attributes) to form the primary key break first normal form? (I am assuming that this is handled by the DBMS, and so the attribute ...
2
votes
1answer
74 views

How to maintain Database audits?

I am using MySQL database (with Django ORM). I want to maintain the database audits similar to StackOverflow, Quora, Wikipedia, etc. These websites maintain the revisions of the changes in the ...
0
votes
2answers
45 views

What is the most efficient way to make a Locality Entity (Town, City, Village etc) with a Country Entity?

I am new to Database. I have a database with a UserProfile Entity. I need to add a Country and Locality(City, Town, Village) to the UserProfile. What is the most efficient way of doing so? This is ...
1
vote
1answer
28 views

Need help making a sane database schema

I am writing a backend for an application that has 4 main types of content: a message, a post, a thread, and a thread reply. Right now, these are all stored in separate MySQL tables: a table for each ...
1
vote
0answers
29 views

Multiple person roles in database(how to define if same person)

I have the following data structure: For internal purposes I can register my outside partner. In this case I don't know his/her identify card number. So I add my partner to database but without id ...
1
vote
2answers
55 views

Why not have Persons for every person in schema?

I noticed that a lot of database schemas often show like, "students" or "teachers" or "employees" or "passengers" or something to that affect. Why not have a schema where every individual is a ...
0
votes
1answer
31 views

designing database where users can have multiple positions

I am trying to determine the best model for the following case: There are multiple offices Each office has multiple staff, each of which have a role (chief, deputy chief, etc) Each office also has ...
2
votes
1answer
34 views

Table attribute for sorting

I have 3 Tables, a User table, a Tracks table and a Likes table. Users can like as many tracks as they want and one track can be liked by multiple users. In my Likes table I have two foreign keys to ...
1
vote
1answer
56 views

Discover missing foreign keys and/or indexes

We have a pretty big database (around 700GB of data), which has been inherited to us, and has had several DBAs. With time we've realized that there are several missing foreign keys and indexes. I'd ...
1
vote
1answer
51 views

Use only one Junction Table per Database?

I am currently working on a Database-Design for an insurer. Example-Entities are "Customer", "Address", "Vehicle", "Building" etc.. So there are many Relations and every Relation has some Context. ...
5
votes
0answers
124 views

Why should I “Avoid partitioning dimension tables” on SQL Server?

SQL Cat have a list of tips titled Top 10 Best Practices for Building a Large Scale Relational Data Warehouse. Under section 4 - Design dimension tables appropriately they state: Avoid ...
2
votes
2answers
69 views

How to join to a single list of IDs from two different tables

I have worked with T-SQL in multiple DBs, but I have never designed a database before. I am designing a new database in MySQL which will track humans and organizations, including financial ...
0
votes
1answer
39 views

How to design a table for reconciliation

A Bank B sends a set of transactions to Bank C every few minutes. Same transactions could be sent multiple times (Bank C should recognize those transactions and should not process them). At the end ...
2
votes
1answer
58 views

Is this table in my ERD redundant? (Image)

I am developing a research article database for a project. My professor approved this design but I feel like the Author's_University table is redundant and that University_ID could just be added to ...
2
votes
2answers
88 views

Does any SQL engine have navigation support in CHECK clauses?

Imagine a simple database to track personal spending. The most basic form of the model should let you create any number of accounts and any number of transactions (specifying the spending amount, date ...
0
votes
1answer
74 views

CALCULATE TOTAL NUMBER OF ESTIMATED HOURS [closed]

I had a question regarding a small task I've been assigned. Below I have pasted the question and a screenshot of the database layout. Now I just don't understand what exactly I'm meant to create, a ...
0
votes
1answer
42 views

Table design for a dictionary that can have words with many different spellings

I'm working on a small, personal dictionary database in Microsoft Access (the 2013 version). There are a lot of words in English that have two or even more spellings. Realistically speaking though, ...