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

learn more… | top users | synonyms (4)

2
votes
1answer
97 views

Confused on how a value gets added with the DEFAULT constraint

A recent definition I've read about Default Constraints: DEFAULT is specified for a column to assign a column a specific value if no value is specified when a row is added. I believe I've seen code ...
0
votes
0answers
107 views

Efficient way to fetch/update X records in a loop

What I need is a simple process that handles requests. I have setup a simple table as follows: CREATE TABLE Requests ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, request_param VARCHAR(250) ...
1
vote
1answer
112 views

Need help with data model

Schema does not list all fields, but only the relevant ones User adds credits/funds to his account using paypal or Internet Banking (which has to be verified manually, and approved, so the 'status' ...
3
votes
2answers
253 views

How to make a better Data model

I am creating a database system using MySQL for a group in my school (a University). I am new to MySQL and its usage.I have started with the data modelling process, since this process will be the main ...
2
votes
1answer
406 views

3 way junction table and redundant value

I was constructing a conceptual design for database in relational model until I stumbled upon some issue. My progress so far is: Asset Type Table +---------------+-----------------+ | asset_type_id ...
2
votes
6answers
4k views

Efficiently storing irregular/repeating intervals (think calendar/events)

I am developing a service that relies on users being able to recieve messages that they themselves choose. These messages need to be stored somewhere before they are send for processing. Right now ...
0
votes
1answer
381 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
677 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 ...
1
vote
1answer
303 views

Resolving Cyclical Reference in Referential Relationships

I'm designing a small SQL Server Compact Edition database and am attempting to enforce referential integrity. The database is a simple backend for a small desktop application that functions as an ...
3
votes
1answer
1k 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
71 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 ...
2
votes
1answer
65 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
41 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
513 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
110 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
64 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
129 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
154 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
3k 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
272 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 ...
26
votes
3answers
4k 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
48 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
230 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
101 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
532 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
89 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
277 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
507 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 ...
5
votes
1answer
16k 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
298 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
595 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
3k 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
34 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
64 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
46 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
62 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
523 views

How to organize the tables of a MySQL database with several statistics data of several years?

I am developing an app that must provide statistics information about the distribution of the money that a person spends each year in taxes in the city where he lives. The user should select the ...
0
votes
1answer
57 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 ...
1
vote
1answer
1k 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
105 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
214 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']; ...
4
votes
1answer
501 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
103 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
106 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
85 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
199 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
675 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
2answers
5k views

how to store multiple value in column in sql server [closed]

I am creating job post application. In my first table the columns are This is my master table ID Position jobDescription minExp maxExp LastDate InterviewDate Project HiringManager interviewer ...
4
votes
1answer
248 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
202 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 ...