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

learn more… | top users | synonyms (4)

0
votes
4answers
124 views

SqlServer: can one table's PK be the FK of other two table's? [duplicate]

I have three tables project, users and proj_user. The PK of proj_user table is the FK of project and users table's pk e.g.(project_id,user_id) My question is, is it possible for the project_id and ...
1
vote
1answer
497 views

Friendship and Messages in a MySQL table relational schema problem (concerning big data manipulation)

Before you start suggesting this question for duplicate read it, I have searched and i haven't found any proper solution. So, i'm building a social networking site (I know that you are laughing ...
0
votes
3answers
126 views

Column A or Column B required MS Access DB

I'm in the situation where I would like to enforce the requirement where a record must contain either one column or another at a data level, I'm not sure whether it's achievable. I've got an Enquiry ...
3
votes
2answers
63 views

Fetch all words from column with like

I have a column with description and inside it can contain # value . what I want is all the words searched from that column with #tag This is my query SELECT description FROM photo where ...
1
vote
0answers
136 views

DB Design: Looking for Performance improvement with a Column used in all SQL Queries

I recently got added to a new ASP .NET Project(A web application) .There were recent performance issues with the application, and I am in a team with their current task to Optimize some slow running ...
0
votes
1answer
461 views

Design for an Asset Management System

I'm attempting to design a database to track IT Fixed Assets and Consumable Parts; what is the best practice for this? Should I design separate tables for Fixed Assets and Consumables, or should I ...
1
vote
2answers
254 views

Relational Model vs. Database Products [closed]

The formal Relational Model (logical level) for databases is not exactly the same thing as you get with MySQL, Oracle or other major products / implementations of ‘relational’ database management ...
0
votes
3answers
88 views

What's the proper design for the table that will hold the processor load stats?

Let's say that I need to implement a widget on the site that allows the user to view the processor load stats via a chart. The user will be allowed to choose how detailed the statistics are: with ...
3
votes
2answers
390 views

Separate table for 'scheduled payments' and 'completed payments'?

In a previous financial payments related database I'd worked on, it just had one large transactions table. We would insert scheduled transactions for the future, and when that time came, we'd execute ...
1
vote
1answer
333 views

use of crows foot or chens notation to draw an extended ER diagram

I am trying to understand if we can use crows foot or chens notation to get an extended ER diagram. or does extended ER diagram has its own notations that should be used ? From what i understand, ...
0
votes
0answers
23 views

Design and queries for analysis of event-sequences

I will have to store sequences of events with certain properties in a database (MariaDB preferably) and I have some issues regarding the design and queries. The crucial part is the analysis of the ...
0
votes
1answer
134 views

When to use a multidimensional relationship in an ERD?

When to use a multidimensional relationship in an ERD? My textbook is in Dutch, and the two paragraphs that explain about multidimensional relationships is too vague and brief. I don't understand when ...
1
vote
2answers
54 views

DB for storing simple time-stamped values

I don't have that much experience in databases but I need to accomplish this as best as I can. It is rather specific. Database will contain only data structured like this: dataID-timestamp-value ...
1
vote
1answer
101 views

“Transitive” keys

I have a central table, A, which will have a key that I'll call A_ID. Now, I have a table B in a "one to many" relationship with A (many B's map to one A). Each link will, of course, have a link ...
2
votes
4answers
30k views

How to represent foreign key in an ER diagram?

Suppose I have a 'Transactions' table which has a column 'Customer ID' (Foreign Key) and a Customer Table having 'ID' (Primary key). How do I show the relation between the two tables and showing that ...
2
votes
1answer
3k views

Execution plan index suggestion - difference between similar queries

I am trying to analyze queries on this table: CREATE TABLE [dbo].[Values]( [tid] [smallint] NOT NULL, [t] [datetime2](3) NOT NULL, [v] [real] NOT NULL, CONSTRAINT [PK_Values_Unique] ...
0
votes
1answer
123 views

Database structure for bonus system in MySQL

I have a problem with creating the structure for my database in MySQL as I'm new in creating databases. The problem is in this: there are three tables sellers, clients and bonus. Table sellers stores ...
1
vote
1answer
262 views

How to improve this product rental schema?

I have a rental system where each product's stock item can be rented from a location and returned to a different location (think Alamo, Hertz etc.). When saving an order, first an inventory event is ...
4
votes
1answer
5k views

MySQL - varchar length and performance

Is declaring VARCHAR size make sense for performance? Is there any difference (in speed) between VARCHAR(50) and VARCHAR(255)? Or defining length is logic/design constraint?
3
votes
2answers
314 views

Peer Review of Database Design

I'm working on a movie inventory database. This is a personal side project for me. I can use what I have, but I would like to make it the best design that I can while learning more about databases and ...
0
votes
1answer
306 views

Fetch data from database from multiple tables matching condition

I want to fetch data from the database. I have three tables in my database like listing_master_residential and listing_master_condo and listing_master_commercial. There are three tables and there is ...
0
votes
0answers
34 views

Completion of ERD, uncertainties about generalization/specialization and primary keys [duplicate]

I'm practicing making conceptual databases and I would like to know of I'm on the right track. I've been trying to create an ERD for a scenario as follows: A hotel can take bookings for weddings, ...
1
vote
1answer
62 views

Building dependence between regional data (Street -> City -> PostalCode -> Country)

I'm in the process of designing a database to contain regional data to be used inside a CRM system. So, a user suspects that things like the city or country are automatically filled (or maybe just ...
0
votes
3answers
559 views

Completion of ERD, uncertainties about generalization/specialization and primary keys

I'm practicing making conceptual databases and I would like to know of I'm on the right track. I've been trying to create an ERD for a scenario as follows: A hotel can take bookings for weddings, ...
1
vote
2answers
53 views

Need database design advise

I attached the DB schema, so it will be simple. Problem: Need to get the item records from the company e.g. 'A'. Should I add to the item table company_id field? Will it be a redundancy info as we ...
4
votes
2answers
8k views

PowerShell Script To Identify Databases

I have enough PowerShell skills to get off the ground, but have none with SQL server. Can someone please point me in the right direction of how to use PowerShell to get information on remote SQL ...
1
vote
2answers
88 views

Database Design for Preserving Values and Additional columns

My colleague and I are having some trouble coming up with a database design, and I am doing my best to stay away from EAV for now. We have an entity called Preparation that stores configuration data ...
0
votes
3answers
544 views

Best way to create relationship between multiple tables

I have a question about how to best handle a table that would be shared among multiple relationships, but shouldn't really be joined with all these tables. For example, suppose I have a table that has ...
2
votes
2answers
621 views

Best practice database design - user rating tables

I want to create a table to store user ratings of items for collaborative filtering algorithms. So far my table looks like this: IID | UID | Rating i.e. item ID |user id |rating. From ...
2
votes
2answers
580 views

SQL order by query results in any arbitrary (but reproducible) manner

For testing purposes I need to get some data from a set of database tables in any arbitrary (but reproducible) order. The idea being that I can later compare two runs using a textual diff tool. Is ...
1
vote
1answer
1k views

Multiple User Types - DB Design Advice

I'm developing a web application that will support user authentication with corresponding roles for each user. Also, my users can be of different types and have different fields associated with them. ...
1
vote
2answers
160 views

Complicated data

I have a requirement i got from my father. He gave me a table and wanted me to digitalis it. So that he can the system can do all the dirty jobs for him ( that's why there are developers in the world, ...
9
votes
2answers
12k views

Disable all constraints and table checks while restoring a dump

I've obtained a dump of my PostgreSQL database with: pg_dump -U user-name -d db-name -f dumpfile which I then proceed to restore in another database with: psql X -U postgres -d db-name-b -f ...
0
votes
0answers
3k views

Need Help in DFD Diagram for online hotel booking system

Hi friends, I am creating a data flow diagram for online hostel booking system. not sure if i created a correct diagram or not any help would be appreciated. thanks in advance
2
votes
1answer
117 views

Best way to design a database with one person with different roles

We've been browsing for a while looking for an answer to this, but any of them suited exactly my needs, I hope is a relevant question. We have a database for managing books. In this database, a same ...
1
vote
3answers
96 views

Best approach for Legal/Natural can have many Orders

I'm working on this model: where as you may see one NaturalPerson can have many Order and the same for LegalPerson the only difference between NaturalPerson and LegalPerson are a few so in that ...
2
votes
1answer
753 views

Calculating a running balance to store with transactions

I need to put a read-only copy of balance and transaction data from a legacy system, which will only be used to view/print statements. Balance | id | member_id | product_id | balance | | 1 | ...
1
vote
2answers
9k views

Online Hostel Management System ER Diagram

I have created an ER diagram for an online hostel management system. Please advise me if this diagram makes sense or needs improvement.
1
vote
2answers
235 views

How to architect an analytical BI platform? [closed]

I am working on a new BI project for a medium sized corporation. Right now there is no Analytical infrastructure in place and the reporting is done manually in Excel. There are a few different data ...
6
votes
1answer
189 views

Many-to-many relationship with attributes

I'm working on a project where I need a book database. The problem is as follows: A book can be related to multiple people (authors, editors, traslators, etc.). Then a person may contribute to ...
1
vote
2answers
328 views

How to store user's language knowledge (150+ languages) in mysql DB

Users in my website must be able to select languages they know (English, German, etc), so I have a table languages with 150 languages (id, name) and table users (id and a lot of other fields). ...
2
votes
3answers
752 views

Removing chat history for a particular user from database

I'm currently developing a web chat application using SignleR and SQL server this is my database schema so far I've added an option where the user can Clear Chat history for a particular ...
0
votes
0answers
61 views

Oracle 11 table design for better performance for reports?

We have a requirement to design a report page. A few factors we have are: number of fields in report is 70 computed fields is going to around 14*8 (based on toggles) a filter is going to have ...
2
votes
3answers
392 views

Tracking events in a task database

In designing a task database with the usual set of tables (Users, Tasks etc), I'm wondering if there is an accepted way to track changes to fields in the task table? I could just provide a text log ...
8
votes
1answer
771 views

Adding nullable column to table costs more than 10 minutes

I have problems to add a new column on a table. I tried to run it a couple of times, but after more than 10 minutes running, I decided to cancel the query because of lock time. ALTER TABLE mytable ...
1
vote
1answer
43 views

Should I drop this column or is there a query that can achieve a results table without duplicate columns?

I use a column named active in several of my database tables to adjust whether a particular entry/row/user/etc should be included in future query result tables instead of deleting the information. ...
0
votes
1answer
34 views

Separation of users columns in another table

I've got a quite a lot of columns on the users table and I have been cleaning it up recently. Now since I'm searching over the users I figured it would improve performance if I separated verification ...
0
votes
0answers
21 views

How to model mappings at various levels

I'm trying to design the database model for my application and I'm finding it difficult to design the mappings for user access to various entities stored in the database. If access was only ...
0
votes
0answers
169 views

Storing DB credentials in an application source code?

I am making an app which connects to a DB, and the app will have plenty different userslogins. Is it a good design choice to have a specific DB login/password for the application, using which the ...
1
vote
1answer
455 views

Unique constraint to enforce max one null per item

I am using PostgreSQL 9.3. Suppose we are manufacturing some items of certain types, at any moment we are producing at most one item of a certain type. The following table captures our history of ...