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

learn more… | top users | synonyms (4)

3
votes
2answers
46 views

Table with N associations

Suppose I have three tables: meta_tags, users, posts. (just an example) Users and Posts both could have tags, so my meta_tags table could be structured like meta_tags - user_id - post_id - ...
1
vote
2answers
140 views

Efficient workflow/queries to save unique information?

I am saving company information in a database. There is a single table with an auto-incrementing key that is unique to each company. There are separate tables for emails, websites, phone numbers, and ...
-1
votes
1answer
85 views

What are static tables & dynamic tables?

I heard the term "static table" when I'am learning about dirty reads in database. Can anyone explain what is a static table and what is a dynamic table ? Thanks in advance.
0
votes
1answer
43 views

Database design for changing data

I'm trying to design a database to store information about an entity. This entity will pass through 5-6 stages. In every stage may happen some property modifications. It is necessary to have access to ...
0
votes
0answers
46 views

Should I make a new table to store date?

I want to store date on which users sign up on my site. Since on a given date several users will be signing up, there will be repetition of data. Should I make a new table to store date to make it ...
3
votes
2answers
251 views

Non unique one to many relationship

I am a noob to database design so bear with me. I have two tables in Microsoft Access: Table 1: | ID | Field1 | Field2 | |:---|--------|:-------| | 1 | A | 1 | | 2 | A | 2 | | ...
3
votes
1answer
102 views

How can I avoid this circular relation?

I based my account/user table design following this article. So I have set my table relationships to look something like this: User <- Memberhip -> Group -> Account With this design, a ...
1
vote
1answer
41 views

News Database Design for a Desktop Application

I am making an application that will process a big chunk of information. This information was retrieved using some web crawlers and is about news, containing data such as News Title, URL, Publication ...
2
votes
0answers
87 views

Choosing database for large set sum and difference

I am creating application, with one server node and multiple clients. Each client holds Set of Strings, that will need to be stored in their own local database. Once a day, all clients will send all ...
0
votes
1answer
21 views

Multi-level confirmation process

My problem: I'm designing a system, that will have multiple administrators with different administrator levels (level 1,2, ...). When a user logs in and creates something (eg. organisation), all of ...
1
vote
1answer
77 views

Are Database duplication and Mirroring one and the same? [closed]

I am going over the concepts of Distributed database management system. I read about duplication and replication. Then there is also something called Mirroring. I googled to find if duplication is the ...
1
vote
1answer
82 views

Database Design question

I am currently interning and one of my tasks is to design a relational database model for a wedding firm that plans and handles weddings. There is a user log in, and the users will be bride, groom, ...
1
vote
2answers
73 views

How to decide that a new table is needed?

This might seem a vague question but I am going through a bit of a dilemma here. How do you people decide when a new table will be need? For example, when creating a table for storing user ...
17
votes
2answers
413 views

Using MAX text or more specific, smaller type

Someone was reviewing my DDL code for creating tables and suggested, when they saw I saw using VARCHAR(256) fields for text I expect to be pretty small, like a first name or whatever, that I should ...
2
votes
1answer
65 views

Unusual database design - any benefits?

I've started working with an application that has an usual design: In addition to the tables that contain the actual data, there is one metadata table that contains all the information about the ...
0
votes
2answers
48 views

Should every table in my database include store id?

I am designing single database for multiple stores. Every store may have different products and stock. should i have to include store_id in every table in database. my database looks like this
1
vote
2answers
32 views

Best practice for multiple 0,1 to many relationships

I'm an application developer without a ton of database design experience and I'm wondering if someone can suggest a design/implementation for relating one table to one or more other tables. For ...
16
votes
8answers
3k views

Why would a primary key value change?

I have been researching the concept of ROWGUID's recently and came across this question. This answer gave insight, but has led me down a different rabbit hole with the mention of changing the primary ...
1
vote
0answers
33 views

Storing Business Logic in Tables rather than Stored Procedure or Application

The requirement is that once the product has been delivered, user can update the logic as he wants. An example of logic I have is follows- An institute teaches English has following requirements - ...
15
votes
3answers
576 views

Implementing subtype of a subtype in type/subtype design pattern with mutually exclusive subclasses

Introduction In order for this question to be useful for future readers I will use the generic data model to illustrate the problem I face. Our data model consists of 3 entities, which shall be ...
1
vote
1answer
20 views

Changing Key (PK, FK) properties in DROP/Add column statements

I want to change a table by adding a column. So far nothing to see here, BUT I would like to make this column part of a composite key, i.e., I now have a layout table_name( Field_1 datatype PK, ...
11
votes
1answer
217 views

Modeling a database for Video Game related information with multiple many-to-many relationships

Scenario I am relatively new to database design, and I decided to make my own hypothetical database for practice. However I am having trouble modeling and normalizing it, as I esteem that there are ...
1
vote
0answers
60 views

database creation [closed]

I'm just starting using data bases and I want to create a table which will store some values and 2 other tables in it. If I were using java for instance I would create an array list of table document ...
8
votes
1answer
379 views

Best way to store a value which could be multiple types

I'd like to re-ask a question in a more direct and general way: How do you create a table to store values which could be multiple different types? In my case, the values provide diagnostics about an ...
1
vote
0answers
25 views

What frontend program is best for my specified needs? [closed]

I am currently an Electrical engineer and I have been tasked to set up a database(outside my realm of expertise) that will be used by multiple users over a networked drive. There will only be a ...
3
votes
1answer
79 views

Best practices, MySQL table with 150+ columns, many null

I have a table storing property data for thousands of properties, all coming from a feed. (updating at least once a day). I'm certainly not an expert with databases and would appreciate some guidance ...
1
vote
1answer
49 views

Distinguish between unknown and non-present foreign keys

I'm in the situation of importing data from multiple other applications. The data is stored in multiple tables that contain foreign keys. Some of these applications don't provide all the needed data ...
1
vote
0answers
48 views

What is the difference between (Superclass/Subclass) Total and Partial Specialization?

What is the difference between total and partial specialization of superclass/subclass (or supertype-subtype) structures in enhanced entity-relationship modeling?
0
votes
0answers
58 views

Get rank of users based on dynamic points

I have this db structure for my ranking system. users table id username 1 alex 2 john points table id name description value 1 foo bar 100 2 bar foo ...
0
votes
1answer
34 views

Data Relationship between Provinces and Cities

Does anybody have experience or know how to correctly design database relationships for where Provinces ->hasmany cities City ->hasOne Province the problem is different cities (based on geography /...
0
votes
0answers
65 views

Design schema for booking appointment/time slots

I'm trying to figure out the best way to design the database. I already have a Client table which consists of id, name and email. A client can book many dates for appointment and each date has only 3 ...
1
vote
1answer
24 views

Insert column name as value

Consider the following, in PostgreSQL: drop table test ; create table test (result1 text, red smallint, green smallint, blue smallint, results2 text) ; insert into test values ('red',1,2,3) ; I ...
0
votes
1answer
41 views

can i have password for my mysql table

is there a way i can set password on a table i am creating in mysql. i am working with rather sensitive data , and i have to use mysql on a server on which i am obviously not the admin. so people ...
2
votes
1answer
66 views

Placing a uniqueness constraint on a date range

I have a table reservation with columns roomno(INTEGER), startdate(DATE), enddate(DATE) with a primary key on (roomno, startdate). How do I place a constraint on the table such that bookings are not ...
5
votes
2answers
87 views

Using schema + Filegroups or entirely separate database(same instance) to partition some big tables?

I have the following challange: I have a staging database which holds a few big tables (on SSD) but I start to run out of space on the SSDs. I use SQL server 2014 BI-Edition (= Standard Edition for ...
1
vote
0answers
25 views

Database Designs Alternative to Polymorphism in Establishing Relationships

I'm working on a database which handles replacement of parts in a machine. Rules A machine has many components A machine has a set of assembly instructions Many components have many part (screws, ...
0
votes
0answers
34 views

Tag system for multiple Item types and groups Schema

I have a groups table and users table, and a many to many relationship between them. In my app, there are different modules, like question, product,suggestion , etc. Users can create products/...
1
vote
0answers
39 views

pub/sub database schema

I am having a hard time creating a pub/sub database schema with stateful metrics. Saying it aloud it sounds easy enough: "There are many organizations that will subscribe to checklists that are ...
0
votes
0answers
46 views

MySQL - Database Design : Encrypting user id and creating a mask for further internal references

For a food order delivery system that I am working on, I want to make sure my user's private information is well protected; from system admins too. So I thought of coming up with this concept of ...
0
votes
0answers
44 views

Mongo Schema for large time series data sets

I'm not a DBA but a C# developer, my experience so far is with MS SQL, so please keep that in mind. I am trying to design a scheme to store sensor data and have it organized. Each TestRecord would ...
2
votes
1answer
31 views

Optional Parent-Child relationship

I have a problem modeling a relationship whereby the child may or may not be related to a parent. For example. You have a Band database with a Tour(parent) table, and a Gig/Concert(child) table. Now, ...
2
votes
2answers
64 views

How can I represent a list of words and related words in a relational structure?

I have a list of header words and words as below and I am trying to understand how best to represent these in a relational table: Header Other Words Words abandon abandoned, ...
2
votes
0answers
32 views

Could I use both UUID and Sequential ID or is it bad design

Sequential IDs have pros and cons. UUIDs have pros and cons too. Why not use both for each table? (Let's say I have -and will have- no problem about storage space) I could use: UUID for exposition, ...
0
votes
1answer
77 views

Products with different prices in an order

I have a "classic" database schema for order and products: Orders ------ id etc. Products -------- id name etc. Order Products --> Pivot table -------------- id --> some people don't like ...
2
votes
1answer
47 views

Conditional relationships? One-to-many relationship using a pivot table for only a type of entity

I have the following tables: users: - id - name - user_type_id (references id on user_types) neighborhoods: - id - name homes: - id - house_number - neighborhood_id (references id ...
0
votes
1answer
26 views

Checking for BCNF in a relational database through FDs?

Okay so I know that for BCNF, when listing all non-trivial FD's that everything to the left has to be a candidate key. So I have this relation: Person(Id, TFN, Name, Phone) Where Id and TFN(Like ...
1
vote
3answers
131 views

Avoid repeated traversing of hierarchical data when parsing a big tree frequently

I have a hierarchical/tree data structure in Oracle/RDBMS where I need to traverse the tree frequently. The tree is huge and therefore parsing it every time will not be efficient. A better way could ...
1
vote
2answers
43 views

Dynamic script to Drop Indices of a specific column

I´m currently writing an SQL script to update our database and for that I need a script to drop indices that rely on a specific column (because I also want to drop the column). In my script I have ...
0
votes
1answer
70 views

Raise Multiple Exceptions Postgres

I am using Postgres 9.5, and I am trying to create exception handling in a stored procedure/function. I can't quite figure out how to raise the error should any of the 'INSERT' statements fail. If ...
3
votes
1answer
74 views

Basic question: Simplest way to share company databases

My company is looking to access large quantities of data from another company we have good relations with. Currently we are using a web interface to interrogate their information, however it is quite ...