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

learn more… | top users | synonyms (4)

6
votes
0answers
170 views

Single table with more columns vs multiple tables with fewer columns

What would be a better database design for a social network website? A single table with more columns and fewer rows, or multiple tables with fewer columns but more rows? For example: A user can post ...
5
votes
0answers
59 views

Difference between contains and has in ERD

I am working on an ERD which represents the relationships between entities in a Web development company behavioral model. There I have entities like: Customer Service ----------- (like: web ...
5
votes
0answers
118 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 ...
5
votes
0answers
147 views

Which is more efficient b+ tree or search (binary or linear)?

SELECT A FROM T WHERE M > 1000 AND M < 5000; I am unable to judge which of the below is best suited for the above query: Linear search Binary search on M B+-tree with index on M B+-tree with ...
4
votes
0answers
57 views

Social (Classmate like) database schema appreciation

This is a database schema I propose for a networking website like the Classmates.com web site. Do you think the database has all the tables needed for such a project? What would you rather do? The ...
4
votes
0answers
66 views

transaction replication causing too many deadlocks after adding a indexed view - what to monitor in order to avoid this?

I have created the following indexed view and its clustered index: This view was created on a subscriber database and all the tables used in the view are constantly being written to through the ...
3
votes
0answers
41 views

Is this ER Diagram correct?

Requirements: • Each customer has a name, a permanent address, and a social security number. • Each customer can have multiple phone numbers, and the same phone number may be shared by multiple ...
3
votes
0answers
34 views

why some one will use approximate data types for sales

I was messing around with Kartris online shop and I was reading this definition for a product version(you have basic information of a product in one table and many versions of products wich can have ...
3
votes
0answers
155 views

Form builder schema for SQL database?

There are many ways one can envision the solution, from the top of my head: Two table approach: Field table with (name, field_name, type, description, meta) attributes Form table with (name, ...
3
votes
0answers
81 views

One table or four?

I have four entities: Absence { ID, StartDate, EndDate, Days, Hours, Reason } Overtime { ID, Reason, Date (Integer), Hours (Integer) } Time in lieu { ID, Reason, Date, Hours } Holiday { ID, ...
3
votes
0answers
41 views

How do I design a database for different sets of measurements on different subjects?

I'm conducting a research project that consists in sampling different physical quantities on about 30 buildings, most of them single-family houses. Some of those physical quantities will be common to ...
3
votes
0answers
242 views

Replicating databases for local and cloud instances

Forgive me if this is an inappropriate place for this architectural question but I'm not sure where it best fits in the StackExchange family. I have been tasked with developing an online/live auction ...
3
votes
0answers
524 views

How to properly configure pgBouncer transaction level pooling?

What is a good way to determine the number of available transaction connections when using pgBouncer to pool database connections? Also, how should I determine the number of max_connections and ...
3
votes
0answers
185 views

Database describing databases and references to tables

Context The company I work for makes a number of different softwares each with its own database. As they're all about related things (accounting) a good amount of data is theoretically the same among ...
2
votes
0answers
30 views

Design a many-to-many table for scale

I am thinking about scaling issues with many-to-many join tables. I have a constructed example as following: Many users have many wishlists. On every wishlist are many items. If I scale this ...
2
votes
0answers
22 views

Creating UNF for multiple actions

I am creating a database design for manufacture company for my coursework. The database design should be able to do these tasks: Buy items from suppliers. (M - N) Items are used for making products. ...
2
votes
0answers
41 views

Any drawbacks to referencing multiple tables from a single column?

I'm working on a system with the following entities: Projects Sections Activities There is one important constraint that needs to be enforced: Activities are always part of one project. ...
2
votes
0answers
133 views

How do I provide a failsafe for retrieval of data from excel to SQL

So say there is a process in place for importing data from excel spreadsheet to SQL using SSIS. And there are two tables, one with new data and one with data from its previous run. I sometimes have ...
2
votes
0answers
76 views

Networking (classmate like) website database model

I am working on a project that consists in building a networking website like the Classmate web site. I have proposed a database model based on the social media database model presented on the ...
2
votes
0answers
48 views

How to implement search for world-wide Wikipedia/OSM places with alternative-names

I would like to implement a search-backend which returns a location-aware search results of all Wikipedia / OSM place names, while recognising place names in all languages. Thus, if you search for ...
2
votes
0answers
54 views

Design Question - Stock and OnSale Table

Background : I'm designing a solution for a repair toy association. Since they have office - I'll use Access. Plot : Stock will store items - I choose this primary key (Item_Id,Record_date) ...
2
votes
0answers
42 views

How To Manage Random Data Without Repitition?

We have a database design problem of a Learning System. Please can somebody help us ? These are the requirements : A person can register for a module which has "n" number of questions. The person ...
2
votes
0answers
123 views

How to store data where various subcategories have specific columns?

I'm new to database designing. I'll use MySQL and PHP. I'm designing a database in which I have a lot of categories and subcategories like given below: Product Table p_id p_title category_id ...
2
votes
0answers
121 views

Database Design: Include Question Answer and Comment tables into Post table

I'm designing a database. This system has question, answer and comment. Question has some type of questions. In each type, there some differences so they're have some difference fields. Answer and ...
2
votes
0answers
73 views

Improving performance of a weighted search query in SQLite3

For an intra-site search I have this table in an SQLite3 database, that relates ids to search terms with a certain weight: CREATE TABLE search_index ( id INTEGER(7) REFERENCES other_table, ...
2
votes
0answers
188 views

Why can't we have multiple cascade paths?

You can see that many questions have being asked about multiple cascade paths. For example: http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths ...
2
votes
0answers
75 views

Is the table in 3NF?

I am creating a relational database - a library management system. I want to show an example where I normalize the data from 2NF to 3NF. My example of 2NF looks like The definition of third normal ...
2
votes
0answers
19 views

Filegroup design for Document Table for BLOB Storage

I have a table for storing documents as VARBINARY types in my database (I'm not using filestream). My database is setup with only one data file, but I am wondering if there is a benefit to creating a ...
2
votes
0answers
121 views

Covering Index - Insert Performance

We are running some performance tuning on our database in SQL Server. The database in itself is pretty small with around 13 tables. But there are multi-level relationships between tables. When I try ...
2
votes
0answers
112 views

Storing URLs in a database

I am thinking about storing URL's in my database, but I am not sure whether the way I use is an efficient way to do things or not. Table name : url url_id (Primary Key & Auto Increment) url_value ...
2
votes
0answers
74 views

With SSD's and in memory tables, does a snowflake schema become preferred over a star?

With SSD's being very fast (reducing the overhead of joins), while more expensive (increasing the cost of redundancy due to de-normalization), along-with a focus on in memory tables (again reducing ...
2
votes
0answers
138 views

Identifying functional dependencies

I'm having trouble understanding the logic in use for identifying functional dependancies. Looking at the sample relation below, I understand fd1 - fd3. But when I look at fd4 and fd5, its logic ...
2
votes
0answers
64 views

Constrain referenced rows to two different tables to belong to the same row in another table

Apologies for the potentially confusing question title. I struggled to make it generic! I am designing a wildlife recording database. An observer goes to a site and records the occurences of species ...
2
votes
0answers
93 views

Proper design of routes in Database MySQL

I'am trying to find out the best database design and try to stick with data integrity by using Foreign Keys. But I'm stuck right at the beginning because of limit relations between tables with FK to ...
2
votes
0answers
122 views

Should I use SQL Azure to store product descriptions and links to images in Azure Blob

For an html/Css/JQuery public Azure website to be coded using Zurb Foundation html5 responsive framework, I'd like to use SQL Azure (web edition) to store product descriptions and links to images that ...
2
votes
0answers
80 views

Single table row “sharding/versioning” to customize values

Introduction Hello everyone, I have done quite a bit of searching and I cannot find a solution for my specific issue. There are a few factors that complicate my situation, which I have outlined ...
2
votes
0answers
327 views

Problem with Database for survey Questionnaires

Bellow I am showing a sample survey question for woman. I need your help to creating a Database. Description: Top part of the survey Questions are Project Code, Name... etc which is ...
2
votes
0answers
1k views

which role-permission database design?

I want to manage roles and permissions. Most of designs on web look like this tables: Users Roles UserRoles Permissions RolePermissions Here, what is permissions? I am thinking for such a design ...
2
votes
0answers
141 views

Two different ON DELETE paths, how do I need to change the schema?

For a project in school I'm designing a website that sells items via auctions. Think eBay, but on the complexity scale of school project. We went through the process of making an ER Diagram and ...
2
votes
0answers
127 views

Digital asset management

I'm working in a company that currently develops Media Asset Management system and now we want to improve our system for document files. I have to search nearly thousand .doc and .pdf files and index ...
2
votes
0answers
3k views

Schema for an RBAC (Role-based Access Control System)

I want to implement an RBAC, Flat based. I've decided to base my system on the NIST standardization model. Although all fine and well, I have an inclusion to that design. Instead of a hierarchy ...
2
votes
0answers
617 views

How do I auto-fill data in Microsoft Access 2003?

I am creating a Purchase Order Form at work using Microsoft Access 2003 so that it can be saved and archived in a database and reduce paper costs. These are my current dilemmas: I am unable to ...
1
vote
0answers
38 views

What is a good database design approach for my Online Quiz Android Application?

I have been working on an Android Online Quiz Application with PHP & MySQL. I know what attributes i need however when designing the relationship it becomes complex and confusing when I analysed ...
1
vote
0answers
28 views

Design improvement on multiple references

Im having some 'perception' problem, maybe... I got this table linked multiple times with another small table and maybe is more of a personal problem with the way it looks but I feel there might be a ...
1
vote
0answers
33 views

Multiple columns VS one big-capacity-column?

I have a table like this: // table_A +--------+---------------+------------+ | id | short-text | long-text | +--------+---------------+------------+ int(11) varchar(200) text ...
1
vote
0answers
38 views

Review the ER diagram of a University

A database includes information pertaining to students, lecturers (scientists) and lectures. We store first names, last names and correspondence addresses for each person. A lecture is ...
1
vote
0answers
14 views

Handling Login of SAAS application end users

I am designing a SAAS application with multi-tenant database per-user-per-database with unique sub-domain of subscribed users. The question arises when subscribed user of saas application add more ...
1
vote
0answers
35 views

database design for a filing system in educational website

i'm designing a database for a filing system in educational website. user can upload files ( files_table) user has a library (library_table) user can add a file to his/her library each library ...
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 ...
1
vote
0answers
39 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 ...