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

learn more… | top users | synonyms (4)

1
vote
2answers
132 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 ...
-3
votes
1answer
42 views

How does indexes work in a database? [on hold]

If I have a list of name that are all different and I have to find a specific name,how could indexes help.thi research?How does index work in a database?I have read other discussion but I haven't ...
1
vote
0answers
15 views

How to deal with a chain of several linked 1:n:n*m:n*m*k “join” explosion?

I have a domain model, where I have a "manager" which has 1:n relation with another resource (A). A has 1:n with B, and B has 1:n with C (it makes sense as a relation). However I'll need a lot of ...
1
vote
3answers
293 views

Working NoSQL scheme for a betting app

I'm building an app where a user can bet on the result of a sports game. Basically, we feed the database with competitions and games, and the users can place their bet in the form of a score ...
1
vote
0answers
13 views

Build EER from requirement specifications [on hold]

As a newbie for database, how to build a EER from requirements specifications, and what does it mean actually.Hard to find any resources from internet!
0
votes
1answer
34 views

Set indexes for database used for login and register actions

I am thinking on how to set indexes to improve performance of a database for register and login actions.I have seen that alphabetical indexes are not good so I don't know how to do...Can you help me?...
3
votes
0answers
73 views

Database design for auto-incrementing, grouped, unique hashes?

I've come up with a (hopefully) better way to save unique company data, which now consists of unique hashes for each entity. Basically, there are multiple hashes associated with a company: hash1: ABC ...
1
vote
1answer
66 views

Database design for an exam

I am designing database for an exam, and I am stuck. I do not know how to do it. Here is the relevant information: Student answers 10 questions. Each question has 3 answers. Only one answer is ...
3
votes
3answers
159 views

Database architecture

I am in the early stages of designing a database, and I have little (none) database design/planning experience. The database is part of a job tracking engine.. As jobs are created, they are assigned ...
1
vote
2answers
30 views

Best practice for parent child relation

Suppose, there are some tasks and some sub-tasks under those tasks For example: Task 1 Sub task 1.1 Sub task 1.2 Sub task 1.3 Sub task 1.4 Task 2 Sub task 2.1 Sub ...
1
vote
1answer
31 views

Simple Database Design - Many to many? - First database (newbie)

This is my first database I am designing. However I am getting confused on the many to many relationships. I have a products table, each product can have inventories in many different warehouses. So ...
1
vote
2answers
32 views

How to model that several variants of a merchandise item may affect its price?

I am an absolute beginner to relational data modelling. I hope my question will be acceptable here nevertheless. Let’s say there’s a merchandise. An item of merchandise may have variants. (While it ...
1
vote
1answer
21 views

Using Case with no column result

I made this solution but it continues not returning any value If the user not exists. select case when TypeId = 1 then 1 when TypeId = 0 then 2 when count(1) < 1 then 3 ...
0
votes
0answers
20 views

single Small Table with more rows or single large table with less rows [on hold]

I need to design a table to hold an various records coming from the source. The records are such that they have around 20 fixed columns that are common to all. There are another 10 to 15 columns which ...
2
votes
1answer
43 views

What is the cardinality of entity relationship between Employee and Department?

Recently, a friend of mine asked me this question about the cardinality between a Department and Employee entities, I replied many to many, then he, my friend, showed me drawings from the internet ...
1
vote
1answer
127 views

How to efficiently design MongoDB database for Uber-like application?

I'm designing a MongoDB database for an Uber-like application, but since I'm new to NoSQL concept, I have several doubts. I have three main collections - users, requests and messages. User can post a ...
1
vote
0answers
50 views

How does one map nested multi valued attributes?

Let's say one has an Entity E. E has a multivalue Attribute M1 and a simple attribute A1. However M1 consists again of a multivalue Attribute M2 and a simple Attribute A2. How would one map this ...
3
votes
1answer
1k views

Database design for Magazine website

I need to design a CMS based website using asp.net web form (c#) and MS SQL Server as backend database for a magazine website which will have monthly issue. I am finding it hard to design a database ...
1
vote
1answer
37 views

Booking database design with a twist

I'm wanting to find a way to how I can implement a booking system that lets users put a start date on the booking and generate the end date whenever. Lets take a house renting example. Say I wanted ...
-3
votes
0answers
30 views

What is the optimized query to get max id from a table? [closed]

I have this query: select max(id) from myTable As myTable has one million rows, sometimes the query throws this error Transaction TransactionImple < ac, BasicAction: 0:ffff0acb10a4:126a:...
3
votes
0answers
16 views

How to decipher entity relationships in a Physical Data Model given the corresponding Logical Data Model

Can someone use the entity relationships in this Logical Data Model (LDM): to explain the entity relationships in this Physical Data Model (PDM): and how to specify the foreign keys in the PDM?
-4
votes
1answer
31 views

Relational model 1:1 relationship [closed]

Can a one to one relationship exist without the primary key- foreign key tie up? Please give an example.
3
votes
1answer
40 views

How would i create a report involving multiple many to many relationships

Lets say table A has a many to many relationship with table B, and table B has a many to many relationship with C. Two junction tables are created for AB and BC to form the many to many relationships. ...
1
vote
0answers
10 views

does insert “logs or traces” into RDBMS (example log4j) makes sense? What are pros and cons?

Our developers are inserting Java logs into a table. This table can grow very much (for example debug logging enabled) and I need to to truncate table. Our database is for OLTP. > desc ...
4
votes
2answers
28 views

Database Design (how to handle subclass & superclass)

I'm wondering what is the best approach to this type of problem (I'm a highschooler so don't have practical knowledge on the subject): Let's say I want to store information about employees at a zoo. ...
0
votes
0answers
26 views

In DB2 , what steps need to be followed to improve bufferpool hit ratio for particular table? [on hold]

I have read Scott's article on Bufferpool hit ratios and folly . I am more interested in experts views on steps they follow when the bufferpool hit ratio drops down, to say, 60%. Also, any specific ...
0
votes
0answers
24 views

Design: How to account for a custom product in a transaction item table

This question is related to Database Design: inventory and sales system? So for example a burger is a Product which is made up of Component bun, meat, cheese, pickles etc. When this burger is sold it ...
0
votes
0answers
30 views

Final Unique Index for Person and Companies?

I am creating a persons schema where the the person table must be very simple because it will be father of other tables that completes it, but a person can be fisical or legal, so I wont put any ...
0
votes
0answers
22 views

one Comment table for all content types or many tables for each content type

I have a very similar question with this stack overflow link but since its not answered I had to post it again. I have many place which people can place comment: file download, biography, blog and etc....
1
vote
2answers
29 views

Bridge table question

Should be a simple questions, I just want to make sure. If I had 2 tables I want to bridge, for example food (food_id, foodname, ...) and a plate (plate_id, platetype, ..). When the tables are ...
1
vote
1answer
42 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 ...
0
votes
0answers
101 views

How to provide referential integrity in databases? [closed]

Previously I've used MS Access, so such databases as MySQL and Oracle is confusing for me. I want to know how to provide referential integrity, in Access it was easy (there you should bind tables by ...
2
votes
1answer
338 views

PizzaShop - DB - Normalized Schema

I am trying to make a simple DB in Access for a theoretical pizza shop(the db is for an assignment). What I am trying to do is link one type of pizza to one or more types of toppings. I just wanted a ...
0
votes
1answer
158 views

Database design for products with multiple units

I am designing a database for retail business. There are some products that can be sold in multiple units, for example, pencils can be sold in ea and dozen, paper can be sold in sheet, ream, and ...
9
votes
1answer
239 views

Overcoming the SQL Server Express Edition limitation

Microsoft SQL Server 2014 Express edition has a database size limit to 10GB. Now, is that just for a single instance or the overall size that the edition will allow? Or does that means that I can have ...
1
vote
2answers
71 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 ...
2
votes
2answers
75 views

How do I guarantee inventory numbers are unique across all tables?

I have an assortment of physical objects which need to be inventoried and each type of object has it's own table. For two example objects, let's use 'swords' for the table containing different swords ...
2
votes
1answer
56 views

SQL Server 2008: how do I hide data from a DB Maintainance User?

Our organisation employs a third party company to do maintenance on our database. To be honest, currently they act as our DBA's since we do not have one. In order to do the maintenance work, they ...
1
vote
1answer
199 views

How to predict the growth of a PostgreSQL database

I'm looking for a way to predict growth of the database given its fields, indexes, constraints (as foreign key), number of lines, and so. I searched on Google, but I found only means to determine the ...
0
votes
1answer
213 views

How to manage very large amount of data in mysql

I have a table in MySQL in which I write logs of users in a specified format. It have just 2 columns:id (auto-increment), log. Currently data is very less, so I'm using this. Now there is a ...
1
vote
3answers
130 views

Include logical key on each table vs. join

I'm designing a database that is used to track an inventory item through its lifecycle. I'm currently relating my tables using the previous lifecycle event's ID (for example, a shipment contains the ...
3
votes
2answers
44 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
36 views

Scalable way to track who's viewed a post? [closed]

I'm building an app that needs to check who, out of millions of users, has not received a post. If a post hasn't been seen by many people, this task will be easy. But if a post has been seen by nearly ...
-1
votes
0answers
26 views

What is an efficient way of issuing a join query where the result from the 2nd table must be chosen from a set of rows for each row in the 1st table?

I'm modifying a question/answer forum component for a site and I want to create a page where each question is accompanied by it's top answer in a single panel. Here is an example (scroll down to the ...
15
votes
3answers
551 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 ...
-3
votes
0answers
24 views

can mysqli handle billions of query at the same time? [duplicate]

My curiosity is How big can a mysql database grow and can it handle billions of queries at a go? One more thing, what's the best practice (database design wise) to when creating a portal application ...
-1
votes
1answer
54 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.
8
votes
2answers
236 views

Data Warehouse: How can i query daily snapshots?

I have some snapshots of a database that are not timeseries. For example: Snapshot day 1: +----+---------------+------------+------------+ | ID | Title | Category | Date | +-...
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 ...
0
votes
1answer
40 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 ...