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

learn more… | top users | synonyms (4)

0
votes
0answers
17 views

Data Modelling of product and product variations

I want to model product and product variations using mongodb (mongoose) I am really confused with how shall I model both I need to add stock and price to variations. Variation can be of anything (not ...
3
votes
2answers
56 views

Is it a good approach to duplicate data in a database?

I have a program that needs to store traceroutes (among other data). Here is a diagram that depicts the business scenario: My use case right now is to store the traceroutes using the following ...
2
votes
1answer
72 views

Unique key based on many-to-many pivot table

I have to manage artists and albums tables: | artists | | albums | | album_artist | +--------------+ +--------------+ +--------------+ | id | | id | | id ...
3
votes
1answer
36 views

Store the content book in database, so that it would be updated afterwards or store them as document?

I have some books in (urdu) made with Urdu Inpage(tool for making pages in urdu) like this: problem that these document files are difficult to read in php because they gives unread-able characters, ...
1
vote
0answers
17 views

Time series data and utc conversion

I'm storing data in minute, hour, day, month and year buckets. All data is stored in UTC. Let's assume a client is in PDT time (-07:00 UTC) If the client wants to query for the hour sum of 4/23/2016 ...
1
vote
1answer
48 views

Limit the number of records depends on the part of primary key

I created database for football matches. I have 2 tables with a many-to-many relationship, so I also created a junction table: Referee CREATE TABLE referee ( id_referee INT IDENTITY PRIMARY KEY, ...
3
votes
1answer
42 views

How to represent the optionality of a many-to-one mandatory relationship from the Parent to its two Child entity types in an ERD?

I have a Parent entity type with two Child entities, with a relationship cardinality of many-to-one (or M:1) from Parent to Child. The business rules say that: It is mandatory that the Parent ...
1
vote
1answer
42 views

How to design a database for query builder project?

I have this project in which user can create queries using web based UI in which there will be list of columns and applicable operators. Later users can edit these queries, too. So I need to store ...
0
votes
1answer
65 views

Designing table for document annotation with future columns

I am working on a web application that allows students/researchers to annotate PDF documents shallowly by (manually) counting interesting phenomena. Right now, there is a total of about 45 of these ...
0
votes
1answer
62 views

FK Issue with Fact Table

I have a Datawarehouse star schema. One Dim table is 'DimTweet'. This contains tweet details. For a selected date I want to be able to display the number of tweets. However, I have a problem in my ...
2
votes
0answers
47 views

Clustered index on a fact table

I'm trying to work out what field(s) to use for the clustered index of my fact tables (which all have a similar structure). I'll use FactSales as an example: CREATE TABLE dbo.FactSales ( ...
1
vote
0answers
21 views

Varchar PrimaryKey. Clustered or nonclustered? [duplicate]

I am working on a legacy database. The primary key is clustered varchar(128) and the table contains a high volume of data. Does it necessarily help performance if I change the clustered PK to ...
2
votes
1answer
23 views

Count of range on datetime in MySql

I want to group table entries for each userId only if they are consecutive entries in the table. I have a table like this. id userId transactionTime 1 1 2016-04-13 22:15:44 2 1 ...
0
votes
1answer
25 views

Design question

I want to implement an extension to my following table Object table == building in generall +--------+-----------+------+--------------+-----------+-------+------+ | obj_id | obj_ident | name | ...
1
vote
0answers
43 views

Ternary relationship with history tracking

I have the following entities Procedure -> id_procedure , desc_procedure . Status -> id_status , desc_status . Subprocess -> id_subprocess , desc_process . The rules are: 1 ...
2
votes
1answer
24 views

Avoid circular reference with configuration tables

I've got a few tables that define configuration information. This defines what combination are allowed in the system. However, I've got a circular reference on ProjectName which can cause potential ...
3
votes
2answers
154 views

Best fill factor for GUID clustering key

We have a database that uses GUIDs for most of their columns in a data (non sequential). Does anyone have any best recommendations for fill factors on a GUID database in an hgih insert/update database?...
2
votes
1answer
20 views

Multiple columns from one table rely on one single table

I'm working with PostgreSQL 9.2 and have the following relation between 2 tables: The table town stores a list of town codes. The table perimeter stores perimeters denomination. Towns are almost ...
4
votes
1answer
157 views

Storing Friendships: Should I use a multivalued column to retain Friend Identifiers?

Say I have a table called UserFriendList, which has the columns: Id (set as Primary Key) UserId FriendListIds And let us suppose that said table holds the following data: +----+--------+--------...
0
votes
1answer
35 views

DB Design - 1 column for each dataset OR dataset_name AND dataset_value?

I am creating a database table for storing numeric time-series data sets regarding cities that will be accessed via a web app. Primarily annual, quarterly and monthly data will be stored in it. ...
0
votes
1answer
40 views

I'm a beginner, studying Data modelling as part of my course. I'm having trouble understanding Normalisation. How can i improve?

It was difficult to frame the title given the word limit, so apologies for that. Currently I'm studying DBA and Data modelling as a part of my ICT Apprenticeship course in a Web Development team. I'm ...
0
votes
2answers
91 views

Is it OK to use PRIMARY KEYs from one table as NAMEs of other tables?

My case: I have many organisms (for example human, mouse and etc.) and a few models for each one. All models are independent from each other and I will never query them together. Normally I will do ...
0
votes
0answers
18 views

Trying to fix collation mess

In our MySQL database we have set several collations. e.g. Connection collation utf8_unicode_ci Server configured default collation utf8_unicode_ci Database default collation utf8_general_ci Table ...
0
votes
0answers
5 views

What is the advantage of using Spring beans for DB connection?

I am new to java spring and I'm just wondering if placing the db connection in spring beans has a great advantage?
0
votes
0answers
37 views

NEXT VALUE FOR SEQUENCE inside a CASE Statement

I want to use a new SEQUENCE each year (See here: 'Id' with the format: YYYYNNNNNN with the NNNNNN part restarting each year ) However you can not use NEXT VALUE FOR inside a CASE statement. ...
0
votes
1answer
39 views

Storing mail in database, data type

I've found pretty poor informations about mail databases. My need is to archive mail messages downloaded from a normal pop3 or imap account: I am an ordinary user and I do not own the server, but ...
0
votes
1answer
37 views

Table/database design question

Suppose you have these fields to keep track of in records: (first_name, last_name, state_id, stateid_status, day, month, year, id_status, id, criminal_status, charge, plea, sentence, create_time, ...
0
votes
1answer
26 views

logic/design for storing achievments and progress for users

I have a game with a databse of users, I now want to implement unlockable achievments in my game. How would I best go about saving this data for each user? I am still fairly new to using a database. ...
1
vote
2answers
84 views

Bus timetable Database structure

I am creating a database for bus timetable in android app. I have created four tables: stops, route, detail_route and schedule. columns in each table are as follows. Database structure route (id, ...
0
votes
1answer
55 views

Convert OLTP database to OLAP Datawarehouse

(http://imgur.com/p8Gy67E). Basically I need help to convert that OLTP Model to Data Warehouse in order to be used for further OLAP Cubes. I understand well the difference between the Star Schema and ...
5
votes
4answers
228 views

How can I represent discrete ages in a relational database?

I am using PostgreSQL to store data on a Person. I need to store whether each Person is capable of teaching a Child of each age year, from 0-17. The ages are discrete values, and a Person can have any ...
6
votes
2answers
149 views

Creating relationships between tables that where not specified in the given UML diagram

Good evening! I'm working on designing my very first actual database from the following UML class diagram (in French unfortunately): I'm at the point of creating the relational sketch of it which ...
1
vote
1answer
29 views

Keep files in mongo as part of the data

I want to create a news app that works in the following way: The site will contain articles, and each article with have multiple versions. Each version is a docx file and the article itself does not ...
0
votes
2answers
51 views

How can I create a join on three tables, each from a different database?

Here is a simplified example of what I'd like to do: SELECT a.create_time, b.create_time FROM DOB.dob_state AS a JOIN numID.numID_history AS b ON a.create_time = b.create_time; I'd like to join ...
3
votes
3answers
65 views

Create unique sequence per set of rows sharing the same foreign key value

In my system, I want each user to have an own id sequence for his articles. So, I have created a database that has a table that depicts a correspondence between users and their articles. Here is the ...
0
votes
0answers
23 views

Restrict Front Ended User access to Form entry only

I have a front end database that I want to restrict user access to only enter data into a form. I do not want them to be able to scroll through other entries via the form. How can I restrict this in ...
1
vote
0answers
25 views

How to avoid repetition of columns of same type in database

I am building a database that will save entities such as: orders, cart, customers etc. All these tables share some common attributes such as: a date, the user agent that was used to create the new ...
1
vote
1answer
17 views

Many-to-many tables tracking participation at multiple events

I am working on a side project to track observations of different birds, and the time spent during each observation. Right now I have a table called Observations: obs_id | start_time | end_time ...
0
votes
2answers
45 views

Multiple one-to-one relations

I'm trying to design database relations for archaeological places in a country. There are around 9000 places registered. An archaeological place can have multiple types. Types will be like Cave, Mound,...
3
votes
2answers
121 views

Two names for a column

I have a column named foo in a table. I have constructed queries in scripts, which use this name. I would now like to rename the column in the table to bar. This will break the scripts (there are ...
0
votes
1answer
20 views

Records creates double entry, should only be one

Need an assist on a database design. I'm creating a system to track students in a course. Currently, I have courses. Courses are made up of modules. A given module can be in multiple courses (eg 2 ...
1
vote
1answer
56 views

Standard naming for a credential systems

Is there a standard naming convention in regards to credential oriented systems? (Logins) The only one I could really come up with (in a shorthand form) is using the word "Gateway". Representing a ...
0
votes
0answers
19 views

Help choosing between one and two tables for manually entered and auto generated items

I'm designing a database that merges a legacy system with a new system, all for the same customer. For the new design I need some help whether to use the same table or two different tables. ...
3
votes
1answer
27 views

enforcing data integrity for sequential relations

These are my tables: create table trips ( trip_id serial primary key, trip_nm text ); create table trip_segments ( segment_id serial primary key, departure_ts timestamp with time zone, -- ...
1
vote
2answers
43 views

Parent -> Child -> Grandchild with a foreign key back to Parent, Bad Practice?

Basically the foreign key on options is there to make it so that if you want to know what product an option belongs to you don't have to go through variants. A variant is like Color, Size Etc.. An ...
2
votes
0answers
31 views

Design university schedule system

I'm not sure if this is the right place to ask for tips and improvements in a database design but I don't know about another one. I want to design a simple University system. Below I'll have included ...
1
vote
1answer
42 views

PostgreSQL Foreign Key

Very basic questions. I have 3 tables, very obvious whats the use of them: -------- CONTACT -------- ID (SERIAL) - PK NAME (VARCHAR) --------------- CONTACT_DETAILS ---------------- ID (SERIAL) - PK ...
-3
votes
1answer
45 views

Why different connections in database.? [closed]

I know we can use a single connection for different databases but why an option of multiple connection is provided, in other words, when is the correct situation to use multiple connections when we ...
0
votes
1answer
38 views

Creating a pseudo email database

I am creating a database for Job offers website between Employer and Applicants that required sending Email between the two in MSSQL. Let's say I have 3 tables like these: Employer ID int idetity(1,1)...
1
vote
0answers
40 views

How can I partition records vertically across machines?

I recently asked two questions: (1) how to store and connect partial records in two or more machines, with the record halves correctly associated, and (2) how to determine the increase in time/space ...