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
10 views

Can Graph Databases be used for an Availability and Scheduled Calendar based application?

Let's say you run a Barber Shop. You have 1 chair available and you want a Calendar to show up on your website that shows all the available time slots, so people can self book a hair cut. In my mind, ...
-4
votes
0answers
13 views

How do I convert the quantities exported into millions Single Strength Equivalent gallons (millions SSE Gallons) [on hold]

I am new here and I need expert help :-) I have a college assignment and I can't figure it out. I will copy and paste the problem along with 2 images. Thank you for your assistance: I am not sure if ...
-2
votes
0answers
26 views

Stock table Database Design [on hold]

I have to design a stock database of various products for different entities i.e. Distributor, Manager, Field Force. The Distributor allocates some products from his stock to manager and manager ...
-1
votes
1answer
18 views

Advice books/courses about Architecture and constructing of a Database System [on hold]

Please, suggest any books/papers/courses about database systems architecture and development. How they work inside. Something like this Architecture of a Database System. Any works like famous ...
0
votes
0answers
14 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
27 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
17 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
52 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 ...
-1
votes
0answers
9 views

Merge two databases, postgres [on hold]

I have two postgres databases, Consider the following situation: Database A: Table 1 Table 2 Table 3 Table 4 Table 5 Database B: Table 1 Table 2 I want to insert the same Table3, Table4, Table5 ...
1
vote
0answers
24 views

Trying to normalize a legacy database and get away from unusual XML validation

I have inherited a legacy database from around 2000 which uses XML in an unusual way to perform data validation. This database is intended to hold product data for an intricate product line when a ...
12
votes
8answers
4k 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
15 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 ...
0
votes
1answer
38 views

Database Normal Form (financial statement)

I would like some advice on how to design the database for financial statement data which objectively addresses the tradeoffs between usability, performance, maintenance and adherence to normal form. ...
2
votes
2answers
66 views

Associating on the ID of an association table

Sorry about the title, I couldn't find anything better. Suggestions are welcome. Say we have two tables, Suppliers and Products. The same product can come from many different suppliers, so we create ...
1
vote
2answers
27 views

Schema/Structure to handle secondary computed data

I'm trying to design a LAMP-based website for my pinball league, which plays competitive pinball every week in match play. One of the features of our league is that we convert raw scores to ...
1
vote
1answer
23 views

How to design tables for airbnb style booking system

all. My English is not very good, but I will try to describe my question as clear as possible. I want to design a airbnb(https://www.airbnb.com) style booking system, this kind of booking system ...
0
votes
1answer
32 views

schema for mysql database with 30million rows

We am building a SaaS backend for restaurants. We integrate directly with POS, so each POS keeps sending customer orders that we store in a following table in mysql. CREATE TABLE name ( `id` ...
3
votes
1answer
69 views

Normalized Data Store - Confused with prefixes to use

I'm designing a Staging+NDS+DDS Data Warehouse system, where an ETL is going to normalize data from [Staging] and load it into [NDS], which will hold all history. I've pretty much finished the T-SQL ...
-2
votes
0answers
21 views

Setting up a centrally based server repository (database) [closed]

I need to set up a centrally located database (server). I need to host it somewhere central so that it is accessible from all other locations of our organization. So that the data can be viewed and ...
1
vote
1answer
30 views

Dependency preserving and lossless join decompositions

I have a relation: R = {A, B, C, D, E, F, G, H, I, J} And a set of functional dependencies: G = {AB -> C, A -> DE, B -> F, F -> GH, and D -> IJ} I ended up decomposing into two ...
2
votes
1answer
24 views

Database table to keep track of objects

I have a table called FoodMaker and another table called Settings. Every FoodMaker has multiple Settings. I also need a 3rd table to record what has been made, when and with what Settings. It might ...
0
votes
0answers
24 views

identifying the functional dependencies

I have been reading up on database design and I am having some difficulty constructing functional dependency diagrams from tables. I have the following table. Each salesperson is identified by a ...
0
votes
0answers
19 views

why this FD make 3NF?

Question like this: Consider the relation scheme ABCDEF satisfying the following functional dependencies: BC --> ADEF CD --> B E --> D BC is a key and designated as primary. Explain why the ...
0
votes
0answers
45 views

Table design for optimum performance

For example let's say I have to create a table called Employee with columns EmployeeID, Name, DOB, Address, DepartmentID[FK]. So I create the table as follows with a primary key and a nonclustered ...
2
votes
0answers
31 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 ...
1
vote
1answer
37 views

When using underscores, should they be reserved to junction tables? [closed]

Say you have the following tables company (contains companies) industry (contains industries) company_type (contains company types, e.g., Public, Private) company_status (contains company statuses, ...
0
votes
0answers
23 views

Advice on table structure change

I am in the process of converting an Access application that stores and reports evaluations to being web-based using php and mysql. I have converted the structure and data over from Access and I have ...
3
votes
3answers
200 views

Can't convert this type of ER diagram into SQL or relational tables

I have stumbled upon a problem during database design. Let me explain what it is all about: Contract has some simple attributes that describe him, and may have annexes and dynamics of payment. ...
0
votes
0answers
24 views

Is it normal to hard code the input of over 80 input fields? [migrated]

This is my first time trying to do database work from scratch, mostly for learning sake. So I'm not sure how to organize my database and tables. Basically, I want to start with a web form and put the ...
0
votes
0answers
25 views

Calculate Quantity On Hand By Location

How can I calculate quantity on hand by location and validate the proposed location quantities against inventory transaction? Currently, I do not track location. I calculate total QoH via the ...
0
votes
0answers
26 views

Looking for a recipe to merge multiple rows to single row

I have a aggregation table that is written by multiple processors (around 20). Each row has a composite key - location-id, campaign-id,... and aggregate stats about it. I need to merge the ...
1
vote
1answer
31 views

Will switching from Concrete Table Inheritance to Single Table Inheritance improve the performance of my queries?

I currently have an application that allows people to view and share 10 different types of posts (ex SportsPost, WorldNewsPost, TechnologyPost, etc). All posts types are conceptually the same, but ...
0
votes
3answers
68 views

Autoincrement Primary Key For Constants

We are having an issue that is probably due to some poor design choice or lack of understanding of how to use primary keys. We have a simple constants table CREATE TABLE IF NOT EXISTS `constants` ( ...
1
vote
2answers
81 views

Need help designing a database

We are designing a system which holds statistical parameters about a particular user. As the users use the system the statistics change and the updated statistics are stored in the database. So for a ...
0
votes
0answers
36 views

Should I create a Factless Fact Table or Denormalise my table

I am creating a data warehouse from a store database and I have a question regarding the design of my dimensions and facts. In the store database a table exists for Person, Person_Address and ...
1
vote
1answer
89 views

How can I design this database?

So I have a ton of transaction logs in pipe-separated format spread across a few dozen files. Concatenated into one file, it's around 2.4 million lines. Each transaction has a store number and a ...
1
vote
2answers
49 views

A simple question regarding database design that has always bothered me

Say I have a table of schools, and each school is either Public, Private, or For-Profit. As well, each school is either '2-4 Year,' '4+ Year', or 'Graduate'. My initial instinct in this situation is ...
1
vote
0answers
19 views

Ideal DBMS Solution for domain problem

I am having a lot of trouble trying to get my head around how to efficiently store and query a very fast-moving data stream, and I expect to be fairly large, in a way that is fast and efficient. I ...
4
votes
2answers
114 views

How to store immutable, unique, ordered, lists?

I have an entity with millions of instances. Each instance has to reference an ordered list of items. The lists have to be unique so no list will be stored more than once. But once created, both lists ...
0
votes
1answer
24 views

Design: Fitting variable hierarchy of text files into a relational db

I have a large data corpus (several TB) made up of data gathered through customer support queries. I've been asked to set up a database to make this data more accessible, but as I'm kind of new to ...
0
votes
0answers
34 views

Using triggers or php code

I have a table called Salons, which has over 15k Salon names. I have another table which takes count of the pageviews of each salon. My question is that if I want to update reputation of the Salons ...
0
votes
0answers
10 views

How to apply updates in a “Fan out on write” architecture?

If I copy certain writes to different tables for faster read performance on certain queries(fan out on write), how should I design updates? Since the data is spread over different tables, I'm trying ...
0
votes
1answer
28 views

Division Email Template DB Design [closed]

First question for me on the DBA SA site! This is for an Java email notification app using MySQL for backend. I have a small number of groups in our organization, [Marketing, Commerce, ...
0
votes
0answers
15 views

calculating Canonical covers

Given F = {AB -> C, A -> BC, B-> A}, compute Fc AB-> C is A extra? {B->C, A-> BC, B->A} 1st possible Fc = {A->BC, B->AC} AB-> C is B extra? {A->C, A->BC, B->A) 2nd possible Fc = {A->BC, ...
0
votes
0answers
26 views

Tables with same schema in DB for different sources. Bad pattern?

The main purpose of the system being developed is to collect data from different sources, process and standardize it, and then add it to the DB. After information from all sources is collected it ...
0
votes
1answer
40 views

How can I quantify the business value of normalization?

My company is developing an internal application with a SQL server database which was started earlier by another developer. Now I am Looking at the model,the developer has stopped at 1NF. I proposed ...
1
vote
1answer
33 views

Date model for commenting system stored in a key-value NoSQL DB?

I'm trying to leverage Cassandra's strong support for time series data to build a commenting system. So far I'm thinking that each row would be a particular topic, and each comment would be a new ...
1
vote
1answer
25 views

Financial Inst - Model Role+Relationships b/w Person and Account

A Person can be on one-or-more Accounts. For each Account a Person is on, Person has a certain role on that account i.e. Person 1 is primary on Account 1 Person 1 is joint on Account 2 Person 1 is ...
0
votes
1answer
40 views

Postgres JSON field for many to many relationships

I'm designing a schema that has a many-to-many relationship as such: A lead can have several stages and each stage can have several leads. I currently have three tables, with one of them as being a ...
0
votes
0answers
67 views

Best way to handle multiple query keys

I am designing a postgresql database for an online card game. I want to provide users with the option to access their play history - users can see log of their games. The data I want to save has the ...