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

learn more… | top users | synonyms (4)

3
votes
0answers
39 views

Class-table inheritance without flag column

I am trying to implement proper Class Table Inheritance (aka Table Per Type Inheritance) in mySQL. AFAIK it's not offered out of the box so some hacking around is needed to make it tick. Current ...
3
votes
0answers
49 views

Ranking items on a per user basis

I have a MySQL DB with 20k-50k items and I want various users (let's say 1k-10k) to rank these items for themselves, so this is not going to be a global ranking but a per user one. All items are ...
3
votes
0answers
33 views

SQL Server storing predefined value sets along with open values

I have Cars with different Characteristics, Characteristics have values, which can be restricted by predefined set of values or be "open" values (any string). Characteristics and predefined values ...
3
votes
0answers
83 views

Best design to reference multiple tables from single column?

Proposed schema First and foremost, here is an example of my proposed schema to reference throughout my post: Clothes ---------- ClothesID (PK) INT NOT NULL Name VARCHAR(50) NOT NULL Color ...
3
votes
0answers
76 views

Is this user-storage database system efficient, effective and secure?

I am using: PHP Version 5.2.17-MIT-BACKPORT-GnuTLS SQLite 2.8.17 I have a database called users, which currently has a table called root. There will be others, once I get all the rows for root ...
3
votes
0answers
73 views

How to relate a [ parent common] table with (possibly many) subtype tables?

I am having a bit of a brain freeze. I have a table whose properties are the root of 4 distinct elements, for example: I need to relate SystemType to a SystemCommon, where one SystemCommon can have ...
3
votes
0answers
53 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
37 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
91 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
199 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
615 views

How to represent directed graph with multiple parents?

http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html provides an algorithm for inserting and deleting from a Closure Table. I'd like to model a similar data structure, except that ...
3
votes
0answers
48 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
262 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
555 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
187 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
41 views

When do you know that you've made a design flaw in a SQL/relational database?

I've been working with a a relational model for a work project over the last few months, and I've gotten to the point where I'm wondering if I've made a mistake in my model's design or implemented ...
2
votes
0answers
45 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 ( ...
2
votes
0answers
30 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 ...
2
votes
0answers
39 views

insert/update from web server or database

I have a data-model on the front-end that I want to insert/update in the database which of the following approaches (or neither!) is considered best practice: # in webserver some_model = {json} ...
2
votes
0answers
51 views

Should I move repeating foreign keys into separate table?

Idea Users can upload lots of items and send them from processing. From the upload settings I can set a channel to which the uploaded items belong to. Channel is a separate entity. Currently I set ...
2
votes
0answers
42 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
27 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
53 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
155 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
86 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
52 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
63 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
160 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
142 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
86 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
297 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
92 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
23 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
132 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
135 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
76 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
187 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
66 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
108 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
125 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
2k views

Database design: Normalizing a “(many-to-many)-to-many” relationship

Short version I have to add a fixed number of additional properties to each pair in an existing many-to-many join. Skipping to the diagrams below, which of Options 1-4 is the best way, in terms of ...
2
votes
0answers
81 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
362 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
146 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
131 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
641 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 ...