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)

0
votes
1answer
87 views

understanding (and modelling) grocery shopping lists

I am having a hard time understanding how multiple product orders are mapped out. What is the best way of modelling a shopping list for multiple customers? I am developing an C# application that ...
0
votes
0answers
82 views

Alphanumeric field in MySQL

I need to create a character field with some rules, they are, Field name: contract_number. Rules: Contract Number Contract number must be alphanumeric. Contract number must be unique. Contract ...
2
votes
1answer
409 views

Designing a reservation system schema

I am not sure if this has been asked before, however I am tasked with designing a new reservation system for my organization We have an older system which simply stored the booking in a table, and it ...
0
votes
1answer
50 views

Fetching User Recent Status

I am running mysql with MyISAM storage engine to store user status(user updates data). To load user recent updates data i am using this query: $limit = 20; $uid = 3; SELECT * FROM userstatus WHERE ...
1
vote
1answer
59 views

Is this diagram possible or correct?

just starting to learn to design. I don't know the term in english to describe this type of design, so I'm here for some help. Can this "specialitzations" have a relation like this one? Thanks!
0
votes
1answer
32 views

Database engine that share a row among multiple tables

I'm wondering whether exists any database engine that can share cells or rows amongst many tables. For example: TABLE Customer ( CID: int Fullname: nvarchar(50) Age: int ) TABLE Group ( ...
0
votes
1answer
52 views

Attempting to identify transitive dependency

Hello I am attempting to identify a transitive dependency in the following diagram. I think that possibly the determinate of "Subtotal" is "Quantity" and "Price Charged" but I'm not sure if "subtotal" ...
1
vote
2answers
96 views

Confusion with Foreign Keys

I've been tasked to build a somewhat basic Prisoner database and I've run into a situation where I don't know what the appropriate route to take is. I have a Prisoner table, that looks like the ...
0
votes
1answer
41 views

Design for hierarchical embedded database

The goal is to construct a hierarchical database of Objects having the following format: TypeA -> ObjectA [values, contains ObjectB*, ObjectC*, etc.] TypeB -> ObjectB [values, contains ...
0
votes
4answers
100 views

T-SQL: Complete Database in one table

at a relational data model every logical relation is a single table with values and so on. I know someone who is this too much work and he wants to create only one table which contains all. This could ...
0
votes
1answer
42 views

How do I “link” two or more rows of the same table with a unique “link” id?

I am looking for a better way to create a link between two or more rows of the same table, but the link itself must have a unique ID. What I am currently doing seems rather convoluted. I have a ...
0
votes
0answers
293 views

Conceptual data model for a billing system

I am working on existing MySQL database, my task is to create a new data model for a billing system on that existing application, I have created a conceptual data model, as given below, My ...
0
votes
1answer
30 views

Database structure for product attributes on orders

I'm making a POS and currently kind of stuck on the order proccess due to the product attributes. As of now the DB looks something like this: tbl_products tbl_attributes (attr id, name, extra ...
22
votes
3answers
2k views

At what point does one database per client become unfeasible?

For one of our systems, we have sensitive client data and store each client's data in a separate database. We have about 10-15 clients for that system. However, we're developing a new system that ...
2
votes
1answer
37 views

How can I model and enforce constraints on a categorical item contained in a collection?

Scenario: There is a collection of items where each item belongs to 1 category. There is a collection of buckets which may contain at most 1 item from each category. Oh, and an item may belong to at ...
0
votes
1answer
58 views

Single MySQL DB contains both push and pull data. Recommended path to replication?

We have a single deployed (MySQL) database that contains both user tables and product tables (currently in the same DB). The product tables are "pushed to production" data -- they are generated in ...
1
vote
1answer
60 views

Databses design, parallel many-to-many relationships. Two tables, or extra fields?

Say I have want to create a database for expenses. I will have a table for holding Person details, and a table for holding Expense details. Then there will be two many-to-many relationships ...
0
votes
2answers
131 views

Help understanding database design logic for a secondhand bookstore project in MySQL

I am currently working on a small eCommerce based project that allows users to buy/sell second hand books to/from other users. I am having a hard time making design decisions on how to model the ...
1
vote
1answer
64 views

Will a specific denormalization increase performance in a specific scenario?

Suppose I have a table with fields | a | b | c | d | ... | z | which contains millions of records and which will serve millions of queries a day. And the field z holds the id of some parameter which ...
1
vote
1answer
106 views

Spliting Datetime into 2 Columns for Restaurant Reservation Schema?

Using PostgreSQL. The primary driver of this is that while no reservation will exist without a date and time, a user can search for and monitor reservations using a time or date or both. With a ...
0
votes
1answer
217 views

How can I Automate MongoDB schema design from existing MySQL Database and Logs?

I am working on the project which will automatically generate sample MongoDB schema analyzing existing MySQL database and logs. I am looking for help regarding the below questions. Please, share your ...
3
votes
1answer
2k views

Fixing table structure to avoid `Error: duplicate key value violates unique constraint`

I have a table which is created this way: -- -- Table: #__content -- CREATE TABLE "jos_content" ( "id" serial NOT NULL, "asset_id" bigint DEFAULT 0 NOT NULL, ... "xreference" varchar(50) ...
2
votes
2answers
244 views

Choosing a database for big data

Let's say I want to write a web service that stores home automation device readings such as thermostats, humidifiers, etc. The data would take the form of: user_id | device_id | tag | value | ...
2
votes
1answer
120 views

Is there any way MySQL can list relationship cardinality between two entities?

I am working on the project where I have to analyse the MySQL schema. As a part of schema information, I want to list down relationship cardinality between different relations. Does MySQL stores ...
0
votes
1answer
599 views

Best Approach to storing chat history?

I'm building a chat application (Yes, another one :P) and am really curious about the best way to store a chat's history(String username, String Message, Time, and possibly string channel depending on ...
1
vote
0answers
25 views

A better model for connecting users to different objects, even efter they cease to exist

I've got the privilege to create a small in-house application for managing todo-lists, although I am not really a developer. (I know some PHP and SQL, and got a chance to prove myself). As my ...
1
vote
1answer
55 views

Need some recommendations on organizing data

I'm new here. This question, to my mind, is allowed by the rules I saw in the "2 minute tour". If I got it wrong, no need for nasty grams. I'll delete if enough people say so. But anyway, I have a ...
0
votes
3answers
39 views

MYSQL Indexes when looking up on alternating sets of columns

I have a table that looks like: CREATE TABLE `connections` ( `connection_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `service_id` smallint(5) unsigned DEFAULT NULL, `parent_id` bigint(20) ...
0
votes
1answer
44 views

SQL tables relations

i'm making a MySQL data base , my tables are: users, activitylog, activitytype. to avoid repetitions i thought about including activitytype in each activity which is logged into activitylog table, ...
0
votes
1answer
33 views

Move into another table or not?

In my web application there exists a web form with certain questions on it. Some of these questions will be pulled / retrieved from the database. Here is some sample data: My question is, is there ...
0
votes
1answer
182 views

MySQL DB structure with Main category and several nested sub categories

I am looking to create a set of fields on my webpage identical to what eBay uses on their category selection page when you are selling something. For my purposes I am doing motorcycles, power sports, ...
0
votes
1answer
71 views

Beginner table design

I have been coding in C# for a couple of years where I usually try to follow the 'single responsibility principle' which states that each class should ideally be responsible for only 1 goal. Now I ...
1
vote
1answer
75 views

Best approach on storing/displaying sanitized data

Surfing the web I find two approaches on storing data in databases. Approach 1: Store data in the database with the html-specialcharacters sanitized $userdata = $_POST['field']; ...
2
votes
0answers
110 views

How to model (a many to many) to many relationship

Hello i am facing the following scenario. I have a student table with the following structure. Student id(PK), matriculation number, first_name, last_name, email A student can have more than one ...
1
vote
1answer
63 views

Database Design

I'm taking a Database course and I'm a bit confused with the different types of graphical representations terminology of a database design. Are Data Model, Crows Foot Notation, Crows Foot Model, ERD ...
0
votes
1answer
81 views

How to design database for DNS?

What tables will be there.Also as read percentage will be significantly larger than write, is indexing on columns advised? What other precautions are necessary? For now, I can just think of 1 table ...
0
votes
3answers
53 views

Item availability in row or another table

I'm a database newbie, so please bear with me. Say I'm creating a library database. I have a table containing information about books. Do you put the book availability as a row in this table or would ...
0
votes
1answer
101 views

Help With MySQL Computed Columns/Auto Updating Field Based On Average

I am currently building a social network type application on-top of MySQL and have run into a problem I can't wrap my head around. In my web application each user has a field called "weight" and this ...
0
votes
2answers
158 views

Is a temporary table a good solution for importing temporary data into a database

i am facing the following scenario. I have a student database in which exam results are stored. Every now and then i get a list with the names of certain students for which i need to find out how well ...
0
votes
0answers
40 views

Unique database presentation ideas

I have completed the first phase of a fairly large database schema design. I built the ER-diagram in DIA. I have to present the schema to an audience of fellow software engineers. Any thoughts on ...
3
votes
1answer
79 views

Help with “Flexible” vs Fixed Tables

We are designing a database in SQL Server to handle sales commissions. A diagram of the current schema looks like this: There are only a couple of us on the project, and our boss dropped this ...
-1
votes
2answers
79 views

What are the possible risks involved in partitioning large database tables? [closed]

There has been much talk about the benefits of table partitioning in Relational DBMS. I would like to know: Are there any risks to table partitioning ? What measures are there to take to minimize ...
0
votes
1answer
112 views

Choosing between single table and multiple table database scheme [closed]

I'm building an application which saves addresses in a PostgreSLQ 9.3/PostGIS 2.1.1 database. Currently I am trying to find an efficient database schema, so far I've come up with two that would ...
1
vote
2answers
149 views

To normalize or not to normalize for few distinct values

Assuming in a Postgres database, you have a table called party, which can have less than 5 well-defined party_types such as 'Person' or 'Organization'. Would you store the party_type in the party ...
0
votes
2answers
57 views

General table name for Person Inf

I was in a meeting with my boss and technical support leader and they asked me for a database model to a very simple application. That application requires: employees information, some location data, ...
4
votes
3answers
182 views

Is matriculation number a good primary key?

I am trying to design a grade assignment database and i am facing the following dilemma. I have a student table which has the following characteristics: matriculation number which i know for a fact ...
3
votes
1answer
69 views

Non-overlapping rectangles constrained to a boundary

I am trying to model placement of parts on a circuit board. Without any meaningful constraints, my basic schema looks like this: create table part ( part_id bigserial primary key, name text ...
1
vote
3answers
315 views

Database throughful design vs Quick and dirty design comparison

Take the representation of an Address, here below is a complete and very detailed implementation: here instead is a quick implementation (which more or less contains the same fields, imagine all ...
0
votes
1answer
45 views

When to use hashtable over binary search tree vice verse for Database Index

Database Index can be constructed using binary search trees (BST) or HashTables , From my understanding so far , HashTables are superior to BST in terms of time taken for searching and insertions ...
3
votes
1answer
207 views

Most efficient queries on flags in TSQL: bit field or table join?

Looking for the most efficient database structure for selecting rows from a large table based on one or more true/false values. For example, whether or not a user has opted in to marketing ...