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
0answers
7 views

What Is the Next Best Ingredient to Add?

I am building a program that allows users to optimize their grocery shopping so they can make the most recipes using the fewest ingredients. One of the features of this program is a function I’m ...
0
votes
2answers
20 views

when to put a business rule in the business application layer or into a database constraint

I need you wisdom, I am debating with another person when is it recommended to put the code of a business rule into a database constraints or inside of the application business layer? There is a ...
2
votes
0answers
19 views

How to store a 64-digit key of alpha numerical values into MySQL Database

I am student studying in Database Administration (first year), one of our task requires a to design a login form for corporate and regular users. Below are the requirements for the username and ...
1
vote
0answers
22 views

Need Help in building database

I am developing an app that send messages (email and sms) to users. The db must also maintains the list of messages send to users. And also the status of messages send to user like send or failed. I ...
1
vote
1answer
20 views

DB Design storing products sold in bulk, priced by vastly different units

I am trying to design a database that stores products that are sold in varying different units. Some are priced by product, some by volume, some by length, some by surface area. Possibly some by ...
0
votes
1answer
16 views

can I use many-to-many relation with weak entity

I have an entity, for example employee(key employee ID), and every employee can have multiple responsibilities. Every responsibility can be tasked to multiple employees. I have 3 options: 1) Create ...
1
vote
1answer
48 views

Recursive CTE to find unique slug

I have an article table where I want the slug to be unique. CREATE TABLE article ( title char(50) NOT NULL, slug char(50) NOT NULL ); When the user enters a title e.g. News on Apple, I want ...
1
vote
2answers
30 views

Optimal indexing for SELECT (mysql)

What indexes would you recommend for the following select (and why)? Keep in mind there can be tens of millions of rows. SELECT * FROM mytable WHERE md5 = ? AND created_at >= ? AND created_at ...
0
votes
1answer
15 views

database design for storing individual users' items

I am trying to make an application which users can have their own account For the account, I have an single table and now I would like to add a function which allows users to items in to their account ...
-1
votes
0answers
29 views

Database design for different wildlife types

I am planning to design a database for wildlife details (such as fishes, birds, and animals) in a certain state, e.g. what kind of wildlife is present. I have created a table in SQL Server 2014 in ...
0
votes
0answers
15 views

Starting out with several databases? [on hold]

I'm choosing which database systems to use for a project that is in its early stages. There are different aspects of the project that fit slightly different models that are best matched by different ...
0
votes
0answers
9 views

Building an economic time series database with aggregation/dis-aggregation capabilities

I am trying to build a database in MySQL and MS Access that contains economic indicators and financial time series. Initially, I wanted to solve the problem by setting up a table for each major ...
0
votes
0answers
18 views

Database design for price comparison site - too complex?

I'm working on a regular price comparison site with two exceptions. I want to show price history and also allow users to subscribe to a product with a specific size and color. As a result, I've ended ...
1
vote
0answers
44 views

How do I maintain categories and products of an optical store?

I have a requirement for an optical store. He has various categories like AgeGroup(Kids/GrownUps), GenderGroup(Male/Female/Unisex), FrameType(Metal/Plastic), FrameShape(Full ...
1
vote
1answer
36 views

Database structure (MySQL)

I have a database called data that is going to contain some data about writers, books and images. I've created these tables so far, but I don't know how to struct the images table. A writer or book ...
-3
votes
0answers
27 views

What is the best database design for blogging service [closed]

I am writting a blogging service that people can make sites in it. But I want to have an echaustive database design to have all sites information in one place. It means for an example I want to have a ...
0
votes
1answer
20 views

what should be the good normalization for the following scenario?

ITS JUST FAKE TABLE DATA TO SIMPLIFY MY PROBLEM I have tables ,, Users , shirts, pants, belts .... each will have some data... so by some research i have made some scenarios of links between the ...
0
votes
0answers
16 views

Accounting - Dimention table in mysql

I am designing a accounting and mini erp system. I use a date dimension table to track all date related data. In oracle we can have many schema for a database. so we could create each schema for ...
0
votes
1answer
25 views

Database structure for a cloud based ERP?

I am designing a configurable ERP hosted on the cloud. Each one of my users(customers) may not have the same table structure. For example: Organization 1 : They store Name, Address, Phone for their ...
-1
votes
1answer
25 views

Best approach for storing huge data [on hold]

I'll be handling 300,000+ users. Each user (after logging in) will be able to create lets say "folders", in each folder, users will create "name=value,name2=value2,name3=value3" entry pairs. Each name ...
0
votes
1answer
31 views

Database structure for users, events and event lists

I am creating a web service for managing events. The service will have users, who are listed in one database, called Users. It will also have events, which are listed in another database, called ...
3
votes
2answers
119 views

Denormalized tables within OLTP database

My company has a product centered around a fairly normalized mssql server database. At this point we have several reports and a web application that needs the data in a much flatter fashion to ...
1
vote
1answer
51 views

Normalized database table with non-atomic values

I'm creating a normalized table for a questionnaire that the customers fill in and in one of the columns is a field called "extra comments". I realize that with the "extra comments" field, the data ...
0
votes
0answers
32 views

draw an ER model using Chen’s notation

hey Guys I need your help with these equations 1- In a faculty office of a department, each faculty has separate printer assigned to her which is connected to her computer. A printer cannot be ...
0
votes
1answer
24 views

What would be most elegant way to replicate data contents periodically into a MySQL table?

I need to parse contents from an API route and insert into a MySQL database. The aim of parsing contents is to replicate the data from the API to my database table. I need to check API response every ...
0
votes
1answer
16 views

Should I use one-to-many or many-to-many to log previous records?

I have 2 tables, company and interns. the initial relationships are, An intern belongs to only one company. A company has many interns I have a slight problem with these relationships. If the ...
0
votes
0answers
25 views

How to do this physical design question? [closed]

Question as follow: Given a disk containing 2^42 bytes. Disk is organized into blocks each containing 2^12 bytes(default for NTFS). Store a file who's records are length 200 bytes each on the disk. ...
1
vote
1answer
37 views

database design for optional association and temporal

I'm designing a simple database and I'm stuck with one part. I have a Task, Sweep and Spray entities. +-------+ | Task | | | ...
0
votes
1answer
12 views

Suitable way to record users requests for usage invoicing/billing in database?

Is there more efficient way to keep a track of all requests made by each user with timestamp? And then filter the requests every fortnight to invoice the users? Example DB schema Table: user; ...
0
votes
2answers
30 views

Field types database design best practice

I am creating a cms system where the user can create fields to use in the templates of the website. I want to create different types like 'string', 'html', 'integer' etc. The user must also have the ...
0
votes
1answer
24 views

Composite Key for Gizmo Tracking

I'm trying to setup a many to many relationship between an gizmo table and a owner table. A owner can have many gizmos And a gizmo can have many owners When I have a linking table do I set the ...
0
votes
1answer
50 views

Comma List vs Multiple Records

I am currently building out an application that will contain many users, two roles and two groups. Logically we want users to be able to be administrators of other users within the two groups. User ...
1
vote
1answer
27 views

How to Implement Different Object Types as Similar Tables?

I have a database I am attempting to design. I am trying to determine the best approach to declare similar, but different types of objects, each with their own sets of properties (perhaps some ...
0
votes
0answers
29 views

Best practices for storing a small list of booleans

For a new application, I am designing a schema in MySQL to hold the data. It manages document information, with many optional attributes. Among them are lists of 3 to 5 boolean values (checkbox lists ...
2
votes
1answer
34 views

Database design tracking daily personal expense

I currently have the following in Excel tracking daily expenses: I would like to turn the following excel sheet into a normalized database. I would also like to capture the descriptions for each ...
2
votes
1answer
94 views

How to model schedule and keep good constraints?

I have some data that I am having a hard time modeling. Particularly, I am unsure of how I can ensure that I have unique data. This is for a schedule where I am assigning teams to stores. A team will ...
1
vote
0answers
15 views

Enforce referential integrity for FriendRequest table that has UserA and UserB

I have a table called User that has UserName as a primary key. I want to establish a relationship with enforced referential integrity between User and FriendRequest. FriendRequest has the following: ...
0
votes
0answers
8 views

testing an extraneous attribute in a relation using functional dependency

I am studying the book "Database System Concepts" by sudarshan . In chapter 8.4.3 in a dependency alpha->beta , If A ∈ aplha to check if A is extraneous, let (gamma = alpha − { A}), and check if ...
0
votes
2answers
30 views

Report individual changes to data

I need to produce a daily extract on the individual changes to a dataset. So, there are 14 tables containing product and customer information and the extract must only show records from each table ...
1
vote
2answers
81 views

Financial database design

I am working on an application to store financial data and need to model the tables where the data for several financial statements will be stored. I came up with the following two designs but I can't ...
0
votes
0answers
12 views

Manager a multivalued attribute?

In the traditional example of a COMPANY database in database design textbooks, we are told that a DEPARTMENT can have only one manager hence Manager should be a single-valued attribute. However, ...
1
vote
1answer
34 views

MySQL created_by or updated_last-by [closed]

I've been giving the task of taking SQL DDL from data modeler and to transcribe it into mySQL for an internship. The only thing I cannot figure out is how to take Created_By VARCHAR2(30) DEFAULT ...
0
votes
0answers
11 views

What pivot tables to use for User Event Registration with Events and Sub Events

I need to design a database for an application to register and review attendees for events. But There is a complication I don't know how to deal with The event they can register for is for a month, ...
1
vote
1answer
27 views

Should I put common fields in each specific tables or one common table then join when data selecting?

Assuming there are a few categories: blog, video, gallery. Each of them contains post data like id, title, content, author, create_at, but different category have few more specific field: video will ...
0
votes
0answers
15 views

How to design a demographics database?

I have a bunch of stores. Each store has the same properties (ethnicity, marital status, income, and so on). Each of those properties has subproperties like (white, black, asian for ethnicity and ...
1
vote
1answer
52 views

one table for all blob with a one to one table for metadata which link to one or many tables?

I'm going to start by saying that I'm not a DBA but i got that role in my current project so I'm doing my best. The application must be able to store files(attachments, official documents, etc) The ...
0
votes
1answer
19 views

Additional info on some rows in the table

I am trying to design an MS Access 2013 database to keep track of an inventory of items. All items have certain common properties, like name, date they were stored, location of the physical item, ...
2
votes
2answers
62 views

Is it ok to have 40+ columns in a table

I am currently re-designing some of DB's for a project. There is one DB in particular that is giving me a little of a headache. The idea is that for every client_id and date (client_id + date would be ...
13
votes
8answers
5k views

Professor told us to store serialized Java objects as blobs instead of defining relational tables

Instead of actually defining a tables with the correct attributes, my professor told us we could map objects to ids like this: id (int) | Serialized Object (blob) 1 10010110110 ...
1
vote
0answers
17 views

reports on history table

Assume Trucks which travel between Cityies. I store the Cityies that a Truck has traveled to in TruckLocationHistory table, if the Truck is passing the City, State = Passed and if it gonna stays for ...