Database design is the process of specifying the logical and/or physical parts of a database. The goal of database design is to make a representation of some "universe of discourse" - the types of facts, business rules and other requirements that the database is intended to model.

learn more… | top users | synonyms (2)

1
vote
0answers
113 views

How to join the tables in mysql to get wall messages and comments with name of person ? Only user and his friends messages

I have four tables in my mysql database as shown in the image url provided. I want to select data from the tables without duplication. I want to show messages and comments on wall according to the ...
1
vote
0answers
33 views

Keys of a relation

I got relation: R = { (ABCDEF) , (A B F -> C, B C -> F, F -> A, F -> B, D E -> E, E -> D)} keys of this relation are: {BCE}, {EF} But i dont get it... why? Why not only {EF}.. it got less ...
1
vote
0answers
604 views

SQLite Design: Better to have many tables with few columns or few tables with many columns?

I am struggling to figure out the best design for a Sqlite database that will hold data about Facebook pages so that I can calculate benchmarks for how the average Facebook page performs. I have an ...
1
vote
0answers
116 views

Relation between lossy decomposition and Natural join?

I am confused with the lossy decomposition and natural join. Natural join(with common attribute): only respective common attribute for the resultant table is formed. Lossy: Natural join on tables ...
1
vote
0answers
92 views

Schema for yearly data models

I am developing a school management system. Most data will not be reused after the end of the school year. I am contemplating using schemas to separate data in various school years, however I am ...
1
vote
0answers
81 views

MySQL: What criteria should I base my table column order/arrangement/grouping on?

Does the order, arrangement or grouping of columns with large (such as long text descriptions) and small (such as bit and tinyint) field types have any noticeable impact on MySQL (InnoDB engine) ...
1
vote
0answers
26 views

Optionally free-form reference

I've seen a few instances of entities which have a field which can be either a reference to another entity or free text. You typically represent this in the UI by having a dropdown widget which can ...
1
vote
0answers
99 views

The correct way of representing folders

I'm trying to kind of emulate a folder structure in my application. Now I've thought of two ways to achieve this, but what is the best practice for that? Table: Attachments Columns: Id (PK), ...
1
vote
0answers
209 views

How to store object with property of generic type in DB

In my business domain there exists an entity parameter. Beside other properties which are identical for each parameter, they have values. These values can be of different data types. There might be ...
1
vote
0answers
113 views

Are duplicates ever acceptable in a database

A warehouse system I am working on allows inwards consignments [type = 0] and outwards consignments [type = 1]. The user selects multiple products for each consignment and then multiple warehouse ...
1
vote
0answers
49 views

Where should a default setting field be?

I am creating a database to track occurrences of incidents on buses based on an existing system. Currently the drivers can set a default bus number, which is used to pre-populate the bus number on the ...
1
vote
0answers
743 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 ...
1
vote
0answers
323 views

Data modelling: RBAC for small enterprise software

I'm building the core tables for a small, cloud-based enterprise app. Is this data model suitable for a role based authentication system (flat hierarchy of permissions) that allows users to work for ...
1
vote
0answers
209 views

When to split a large table

I run the SQL Anywhere DBMS, and we have a table of about 10 columns(normal text, timestamps etc). However, in my use-case, every single day, the table gets 2160000 new rows. It's also crucial that ...
1
vote
0answers
263 views

Database Design: separating reusable & instance data

I'm developing an app which is basically a collection of checklists that have to be processed. I obviously need a database in which the information has to be stored. I just can't wrap my head around ...
1
vote
0answers
124 views

Real Time data collection - Help select Database

My Scenario: I want to collect data from various sensors[for now I have 600 sensors sending data, but this may have to scale up to 3000 more data sensors, all sensors send real time data] Now I have ...
1
vote
0answers
615 views

How to represent class table inheritance (current DBMS-specific way please)?

I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server. I found a recommendation for MySQL here (How do I map an IS-A relationship into a ...
1
vote
0answers
94 views

Unable to decompose this relation to BCNF

I have a relation R = { A, B, C, D, E, F, G, H, I } And functional dependencies F ={ ABC -> DE E -> C AB -> F C -> G F -> H H -> IJ F -> B } I am able to do simple BCNF ...
1
vote
0answers
119 views

key value database

I have one object and a lot of data associated with that object, you can think of it as something like facebook timeline where we have user and user has a lot of posts. I cannot store data in JSON ...
1
vote
0answers
246 views

Improving database design for category based site

I'm working on a site that gets about 30k visits a day and it makes use of a lot of queries which uses UNION's over about 45 different tables, these tables generally range from about 15k rows to about ...
0
votes
0answers
11 views

Database Design Question? Relationships and Form Data

I am working on a project that requires to store evaluation form data. Essentially, the a reviewer will fill out a form based on the performance of the person being evaluated. Now there is well over ...
0
votes
0answers
6 views

Query parent table and get child tables columns

I have two tables, one inherits the other: CREATE TABLE parent ( col1 INTEGER ); CREATE TABLE child ( col2 INTEGER ) INHERITS( parent ); Is there any way to query the parent table, such ...
0
votes
0answers
32 views

A transition table, is it a good idea?

I'm writing an application that stores a project's C++ API representation. For example, I have tables like Class, Function, FunctionParameter, DerivedClass, BaseClass, and so on. Every once in a ...
0
votes
0answers
44 views

What is the name for a table that keeps track of operations that have been performed on the database?

It's name is Trans_Log It has the following fields: TL_User (database user logged in) TL_WinUser (name of windows user logged in) TL_Date (date of operation?) TL_Type (type of operation / function ...
0
votes
0answers
13 views

Primary keys: composite and singular

In general when I design tables I always include a single unique field, even if it there is already a unique combination of columns available to create a primary key. This field is usually either a ...
0
votes
0answers
14 views

How to create and select entities in MongoDB?

I'm a newbie in MongoDB and I'm trying to understand how to create objects there and how to select them in comparison with relational databases. So I need an example how to perform these things. ...
0
votes
0answers
32 views

How to reference a table mapping that is not initially known in another table

I have four tables: ExternalName, InternalName, ExternalInternalNameMap, and Transaction. ExternalName and InternalName have a many to many relationship. ExternalInternalNameMap is the junction table ...
0
votes
0answers
22 views

Uber-wide tables: Time to switch to Mongo?

As a web developer, I've typically only worked with nice, normalized tables in SQL-based RDBMS, but I'm doing some data consolidation for a researcher who will be analyzing correlations between all ...
0
votes
0answers
18 views

Advice for a friendship table

For my little project I would like to create a very simple friendship system. And I would like someone to give me some advice/criticise the current design I have. Here is an example of my friend ...
0
votes
0answers
17 views

School fees database design

I am trying to design a database for a school fee management system. The school is charging some Admission fees , Security for one time but development fund, registration fund on annual basis. but ...
0
votes
0answers
22 views

How to model a relation between table data and table layout?

Hey there professionals, I have a quite nasty modeling problem that I think may also be part of a general design decision issue that I am obliged to solve. I did not do much modeling before and my ...
0
votes
0answers
13 views

How to model a type that's related to one of a number of other types

A generic data modeling question: How would one model the database tables in a case in which an entity of Type A is related to one entity of either Type B or of Type C (but not both). For bonus ...
0
votes
0answers
25 views

Credit Card database schema

I am not very good in database design so please if you can help me with this. I want to know what will be the best way to store it in mysql database Here is the selection I want as final Select ...
0
votes
0answers
13 views

Assigning items to containers

I have two tables for separate things: One table for containers, one table for items. They both have things like descriptions, title, and of course ID fields: container_ID and item_ID. My containers ...
0
votes
0answers
14 views

Should I partition table by time or project_id, or both?

I have a table of event data that is getting slightly large. It's ~10 million records. I want to partition the table to help speed things up. I almost always restrict the query by both the time and ...
0
votes
0answers
11 views

How to add entries to tables in PostgreSQL if the attributes are reliant on other tables?

The problem that I have is something like this: Users ========= UserID Name Surname User Group ========= GroupID LeaderID Group Leaders ============= LeaderID UserID GroupID The problem arises ...
0
votes
0answers
28 views

How to combine 3 queries into 1 including set?

I have 3 mysql queries and each does specific job, the problem is I can't figure out how to combine 3 queries into 1. I'll write down all 3 queries and explain what each does, thus you can figure out ...
0
votes
0answers
12 views

what model should i use for store detections time series in mongodb?

i have cloud site with many hosts and virtual machines inside of it. I receive from some software many different metrics values regarding vms usage (cpu load,free ram,energy consumption and so on). ...
0
votes
0answers
23 views

All in one table or separate tables for each categories for a clasifieds website?

I am developing a classified website by using ASP.net and my DB is MYSQL. In there I am using a header table to store common records of an ad. like ad title, description, published date like wise. And ...
0
votes
0answers
27 views

What kind of data cannot be stored economically in a database / data-warehouse?

Someone mentioned this in a presentation but didn't provide any examples. This stuck with me and I am trying to figure out what kind of data couldn't be stored economically in datbases ever since. It ...
0
votes
0answers
15 views

How to model role and different activities

I am new in the database design and I'm facing this problem, I hope somebody can help me. In my domain we have 3 roles, Non-Supervisor, Supervisor, Manager. I created a Person table and a role table ...
0
votes
0answers
23 views

Manage orders based off product attributes and quantity

I'm developing a database for an online store in which the Products have multiple attributes and attribute types. ATRIBUTES TYPES atrtype_id name (like size, color...) ATRIBUTES atribute_id ...
0
votes
0answers
21 views

linking a single table (comment) to multiple tables (article,link,attachment)?

the main idea is that i can post an article , link or an attachment and i can receive comments on each of these types of posts (the structure of the comment is the same) , the problem here is that i ...
0
votes
0answers
42 views

Multiple user roles with different attributes

I need to design a database that has multiple users, with multiple roles. For instance, a user can be an admin, company, employee, or customer. Each user can have any number of these roles. Right now ...
0
votes
0answers
28 views

How to store likes to posts in Parse.com database?

In my parse database I have a classed called Post and Like. Should I create a field likes in Post class with Relation<Like> type or there is another better option of storing likes in Parse DB?
0
votes
0answers
27 views

How to cast as type of a table column?

I have multiple views with union like this: select column1, column2 from mytable union select cast(extcol as nvarchar2(15)), cast(extcol2 as nvarchar2(10)) from exttable But later I change the size ...
0
votes
0answers
25 views

Database design for varying prices from multiple vendors

I am designing a database where the products are sold in bulk with multiple pricing options, from 4 different vendors. What is the best table configuration to store this information? Id like to be ...
0
votes
0answers
17 views

Trying to Figure out an ER Diagram for a set of instructions

Below is information about a database and I want to come up with the ER Model. (Im not sure if this is the right place to post this. Im new to this page) professors have an SSN, and a name projects ...
0
votes
0answers
42 views

MySQL Dynamic columns duplicate value workaround

As the title suggests i have the following code which transposes certain row values into field names, however the problem im having is that the values "closed" & "reopened" may appear in several ...
0
votes
0answers
23 views

Performance/Maintenance Implications of a Write Heavy Table In An Otherwise Normal “CRUD” Database

We have a small-ish (<100,000 rows) MySQL database (on Amazon RDS). Its supporting a fairly simple read heavy CRUD web application. Currently we're building out a web analytics system. It will ...