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

learn more… | top users | synonyms (4)

3
votes
2answers
194 views

Large scale, hard to model design advice needed

I would love to hear some opinions or thoughts on a MySQL database design. Basically, I have a tomcat server which receives different types of data from about 1000 systems out in the field. Each of ...
3
votes
1answer
594 views

Simple survey database design [closed]

I'm creating a survey for visitors of my event. However it's been a while since I created a database. So I need some help. I found some solutions but they are way too extensive and that is something ...
3
votes
0answers
186 views

Circular relationship [closed]

I'm trying to design a database model and I have a question. I need to represent this: An order could have zero or one QAP. An order could have one or more E-Report. An E-Report must have one QAP. ...
3
votes
1answer
36 views

Normalised database with one-to-one relationship

Is it bad practice to use a 1-1 relationship between two logically distinct tables: the user details and the user configuration settings? Each configuration record would have a single unique user ID, ...
3
votes
2answers
189 views

How do I deal with items that I need to repeat monthly?

I have a table where users will enter transactions on a weekly basis, these transactions will either be single items or will be recurring i.e. rent would be recurring and should be the same for every ...
2
votes
2answers
220 views

Storing data as rows as opposed to columns

Here's a typical way I would store data (obviously not storing the password in plain text) USER TABLE | UserID | UserName | FullName | EMail | Password | ...
2
votes
3answers
2k views

Is a two column primary key the most performant way to go if (a,b) should be unique but I will only be querying against a?

I have a table with two columns, A and B. Each pairing (a, b) should only occur once in the table. When I query this table, I will only be interested in those rows where A has a certain value. Is a ...
2
votes
2answers
1k views

Database normalization: Describing trait - foreign key to table, or varchar field with value?

My colleagues and I are having a discussion about the normalization of descriptive traits in the database, such as "status" or "type". Let's call the central table of the discussion "Order". In my ...
2
votes
2answers
705 views

Can a 2NF relation be M:M?

I have recently learned about 1NF, 2NF, and 3NF. I understand the definitions and the differences. I also had learned earlier about removing M:M relationships from a conceptual model by using ...
2
votes
1answer
3k views

Two-column foreign key constraint only when third column is NOT NULL

Given the following tables: CREATE TABLE verified_name ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, UNIQUE (name, email) ); CREATE ...
2
votes
2answers
187 views

Should I create an enumeration or individual flags?

I'm creating a new database table which will be used to store pictures of products. I'd like to identify the product "type" represented in the picture. Say we have 3 general categories for all the ...
2
votes
2answers
300 views

Proper use of NULL, and utilizing CHECK constraints for business logic vs stored procedures

This question regards the proper use of NULL and utilizing CHECK constraints for business logic vs stored procedures. I have the following tables setup. I normalized the tables to avoid using ...
2
votes
4answers
257 views

What free database software support sequences [closed]

I have a requirement that a database must support sequences. I know oracle and PostgreSQL support it. Are there any other free database software that support database sequences? it would be great if ...
2
votes
3answers
719 views

File name as primary key?

I have a few tables I'm going to create for a website, Literature, video, and image. Is there a good reason why I shouldn't use the file name as a primary key. For instance... "someimage.png" as the ...
2
votes
2answers
685 views

Generalisation in databases

I am working on a Hospital Management project in DB2. I have one table for staff with a primary key of staff_id. The hospital has to have different staff... e.g doctors,nurses,e.t.c. Is it better to ...
2
votes
2answers
304 views

Can different tables have columns with the same name if those columns are not primary keys? [closed]

I know it's a simple enough question but my textbook is very confusing about it.
2
votes
3answers
109 views

Is it better to start with a smaller INT types and grow as needed?

I am designing a new SQL Database for my ASP.net web application and I can foresee that some of the ID columns will get very large in the feature need INT and BIGINT but that will not be for a few ...
2
votes
1answer
859 views

Database Design Cars-Models / Car Classifieds

Im developing a Car Classifieds but im not sure of how is the best way to design something, there are some features of cars like if it have 2,4 doors, if it is 4x4 or 4x2, engine 1.6, 2.0. All this ...
2
votes
3answers
340 views

designing multiple tables while eliminating duplication?

As I'm working on an educational website, I'm facing a problem with my database design scheme - I can't find the correct design that eliminates duplication in the tables I'm creating. What I'm trying ...
2
votes
2answers
100 views

which option is better for not slow down the performance of queries?

I am developing a system that records customer payments, when a customer makes a payment, the system must update the account and show the payment made and all previous payments, is here when the ...
2
votes
1answer
573 views

Which NoSQL database(s) should be used for advertising platform? [closed]

What NoSQL database or combination of databases best meets these criteria? Very fast writes and reads Persistent data that won't get lost in a crash Plays nice with PHP Works well in the cloud (EC2) ...
2
votes
1answer
110 views

Saving and managing multiple database “states”?

I'm very sorry if this is the wrong place to ask this question. I have been tasked with implementing a system to allow administrators to make changes to data in a database, and save these changes ...
2
votes
4answers
144 views

Advanced design question related to compound primary keys

As a database design expert, I had a discussion with an academic person about database design and I need some arguments on why some design is bad or not normalized or bad practice. As we cannot ...
2
votes
2answers
470 views

Is a trade-in its own purchase order, or an adjustment on a sales order?

Let's say you're a car dealer, selling cars. When a customer buys a new car, he often trades in his existing car. What data model makes more sense? A) create a sales order record for the sale, and ...
2
votes
2answers
2k views

Is to possible to forbid access to tables in own schema - Oracle?

Lets assume that we have the following situation: We have an Oracle schema/user: USER_TEST (with the "create session" privilege). SYS as SYSDBA creates couple Tables on schema USER_TEST. Is it ...
2
votes
3answers
51 views

What is the best practice for treating fields with only two possible values?

A column/field will only have 2 possible values, either "Transplanted" or "Direct-seeding". Is it better to create a table for these options and give them unique IDs or are they better off put as raw ...
2
votes
3answers
90 views

Schema for a lease platform

I have following tables: A room I want to lease (has user.id as ForeignKey) A users table for users who can rent it An availability table for time periods the room is to use with prices for that ...
2
votes
4answers
490 views

Are junction tables a good practice?

Say I have a large table that holds the user's info and another table that holds several locations. Then I use another table that holds the user_id and the location_id. In order to retrieve the data ...
2
votes
2answers
51 views

Do I need foreign keys in ORMs?

Object Relational Mapping (take ActiveRecord and Rails, for example) allows a programmer to define relations between models, and callbacks on delete. So my question is: do I still need to define ...
2
votes
2answers
110 views

Is it important to design the database the right way from the beginning?

I'm a beginner web developer and I use MySQL and sometimes MS SQL Server. However, I don't have that much information about databases, just basic information to be able to build some tables. Now, ...
2
votes
3answers
225 views

Which of these two methods is standard when creating a 1 to many database relationship?

If I have a customer that can have many addresses, I can create an Address table with columns Street, Town etc. and CustomerId. Then I can insert multiple records to have multiple addresses per ...
2
votes
5answers
12k views

Coding an accounting database from scratch? [closed]

My new medical practice is picking up fast and my homemade Libreoffice Calc spreadsheet is quickly becoming cumbersome to track patient visits, accounts receivable, overhead reconciliation, etc. Most ...
2
votes
1answer
1k views

How to handle huge table

We have a problem with a few tables that are very very big slowing down the system. so my question is if that size is normal for the amount of rows in the table, and also what could be done to make it ...
2
votes
2answers
671 views

How do I optimize this database design?

I need to create a vehicle tracking database. There are several vehicles that send data, consisting of around 20 parameters, to my database. The parameters of a vehicle include the constants ...
2
votes
1answer
6k views

Using a foreign key on multiple fields in one table

Simple question probably. For two tables. A one to many relation is created from A -> B, TeamID -> Fk_Team, however there are multiple fields that need to reference this one relationship how does ...
2
votes
2answers
72 views

Best index for this scenario

There is a table in our database tblCustomText( ParentID int NOT NULL, CustomFieldID int NOT NULL, Value char(50) NOT NULL, Log_CreateDate datetime NOT NULL ) (ParentID+CustomFieldID) is ...
2
votes
3answers
741 views

Physical Address Schema

I'm looking at adding addresses to an existing database, and am trying to work out the advantages and disadvantages of the following methods (and if there are any I've overlooked). Store the address ...
2
votes
3answers
245 views

Should I create a separate database table for temporary users

I have a user table that controls access to a website. We currently have a few hundred users and this might eventually grow to a few 10,000s. We have a requirement to allow in "temporary" users. These ...
2
votes
1answer
292 views

Where to save user data?

I hope this is the right stackexchange place to ask. Im really new using databases and I have a little question: Where to save the user data? I mean, if Im logged as "Foxandxss" on my app, and I want ...
2
votes
4answers
101 views

Row for each date, or 31 columns?

Currently I have a system whose front-end looks like this: The following query: SELECT `l`.*, `s`.`month`, `s`.`year` FROM `summarylines` `l` JOIN `summaries` `s` ON ...
2
votes
1answer
318 views

Why do/should DBMS cache at the application layer?

This article describes DBMS as caching data at the application layer, which is why they often use Direct IO to bypass the file buffer cache. My question is: what benefits are provided by the DBMS ...
2
votes
1answer
3k views

MySQL - varchar length and performance

Is declaring VARCHAR size make sense for performance? Is there any difference (in speed) between VARCHAR(50) and VARCHAR(255)? Or defining length is logic/design constraint?
2
votes
2answers
512 views

SQL order by query results in any arbitrary (but reproducible) manner

For testing purposes I need to get some data from a set of database tables in any arbitrary (but reproducible) order. The idea being that I can later compare two runs using a textual diff tool. Is ...
2
votes
3answers
342 views

Tracking events in a task database

In designing a task database with the usual set of tables (Users, Tasks etc), I'm wondering if there is an accepted way to track changes to fields in the task table? I could just provide a text log ...
2
votes
2answers
71 views

Give every user 3 default 'bookmark' entries

I've got a table users and a table bookmarks. The table bookmarks obviously contains pages the user wants easy access too. But, there's a new requirement now: every user gets 3 unmutable bookmarks. ...
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 ...
2
votes
2answers
238 views

Most performant way to fetch last record

I have a Postgres table Prices with the columns price (Decimal) product_id (Int) Prices get updated regularly and I keep old prices in the table. For a given product, the last price in the table ...
2
votes
3answers
7k views

Database design to track student attendance

I was having a doubt regarding the design of a database table to track students' attendance. Currently, my table students have atleast 4000 students. Creating a database for attendance to track their ...
2
votes
1answer
167 views

Is it ok to let users add new columns and tables to your database?

I'm basically making a database of databases. An administrative User can go in and hit "Create Database" (which is basically a table) and set up the column types and how many columns. Other users can ...
2
votes
1answer
1k views

Foreign Keys with Composite Key

Imagine I have a table with the following dependency: (Name, BoughtFrom, TimeBought) --> Cost Where the three attributes in bold form a composite primary key for the table. Now then, I want to ...