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.
1
vote
2answers
280 views
Database Design - One table for different types of transactions
Considering a procurement system as an example. Would it make sense to have one single table for the following: Quote, Order, Invoice and Credit Memo?
There will be some details that are different. ...
0
votes
0answers
45 views
How to properly design this type of relationship
I am building a model with the following requirements:
Items may have attributes
Attributes may be of type: 'Choice', 'FreeText', 'Number'
I am not quite sure how to relate the item to attribute ...
4
votes
1answer
128 views
250 billion rows - computing interconnectedness on a really huge scale
I have a table of approximately 700k rows, each identified by a unique itemnumber.
Each row/item can be associated with any of the other rows/items in the table by calculating a single numerical ...
1
vote
2answers
65 views
Table Design - Identical tables for different statuses
I am developing a transactional System and the below is a simplified scenario of my problem.
Users enter JOBS in a system.
A User first enters a JOBS with a DRAFT status (and the Job is given a ...
2
votes
2answers
71 views
Referential Integrity - Re-using table
This is a simplified example of a design issue that I am facing: I have 3 tables: Car, Ship and Bicycle. I need to add an "activity logging" table that records user actions such as deletion and user ...
0
votes
1answer
39 views
Should I create a new table for store information of an associated object with object need to create
I'm not sure if topic can get you understand what I want to explain.
Follow guide in my old question, now I have these tables and its columns:
Course: name,...
Topic: name, course_id
Question: ...
4
votes
1answer
120 views
Modelling Hierarchical attributes
I am trying to figure out the data model for a retailer.
The retailer has several stores across the country and they are modeled using the following hierarchy:
Channel -> Zone -> City -> ...
1
vote
1answer
347 views
How to model entities in an entity relationship diagram?
I'm a beginner Database designer and wondering if anyone can help me understand this.
Let's say we want to design a system that has, among other entities, an entity called Vehicle, and another one ...
4
votes
2answers
126 views
What happens to the index of a primary key after a DROP CONSTRAINT?
I am running PostgreSQL 9.1.4.
I have a table with many existing rows, and a bunch of other tables with foreign keys pointing to it, for which I am trying to :
1 - Remove the pkey constraint on the ...
3
votes
1answer
91 views
which design better when use foreign key instead of a string to store a list of id [duplicate]
Possible Duplicate:
Is using multiple foreign keys seperated by commas wrong, and if so, why?
I'm building online examination system. I have designed to table, Question and GeneralExam. The ...
2
votes
2answers
169 views
Should I use SQL or NoSQL for this specific design (surveys)?
Building a small web application to create satisfaction surveys with a default list of questions and responses. The application is for a small audience of people (5,000 max) with surveys sent out when ...
4
votes
2answers
287 views
Are there any tools to go from ERD to DB design and vice versa?
I'm learning DB design and looking for some tools, preferably open source software, that could help go from an ERD to a DATABASE and vice versa.
For the record, the database i'm using is MySQL.
0
votes
2answers
145 views
How to model a 1..1 Composition Relationship
We have a couple of entities/relationships of the following kind:
Entity Event has a TimePoint
The TimePoint contains time information about the event. It belongs to that event and cannot be used by ...
3
votes
2answers
2k views
Select column names whose entries are not null
I would like to have a list of those columns of a table that have at least one not-NULL data entries in them.
In other words, I would like to get the column names for which the following returns at ...
1
vote
1answer
145 views
mysql session logging tables
I've been building a session logging system for our sites. We get around ~15m hits a year and at the moment we're really only interested in stats about people who logon to our sites.
Our table ...
4
votes
2answers
216 views
Introducing an 'age' category in a patients database
I want to do the following in my DB related to patients:
when patient inserts his birthday it shows his age category as: infants, children, adult, elderly... etc. to help me classify my patients
how ...
0
votes
1answer
61 views
Setup logical Database model for measurements
I'm struggling to set up a logical model for the following spreadsheet:
(See a close-up of the table here.)
My problem is that it is not clear to me how to deal with the "gap" values where each ...
0
votes
1answer
181 views
Keep total transaction in invoice application
I want to make an application called as Invoice Application with MySQL database. Now I have designed this database. Now I want to know is this database good for Invoice Application. I will use MySQL ...
3
votes
2answers
432 views
How to make a trigger that will compare input with a value of other table?
I had made two tables with the following fields:
TABLE 1: "Bookings"
FIELD: "fk_flight_number"
FIELD: "date_of_reservation"
TABLE 2: "Flights"
FIELD: "flight_number"
FIELD: ...
3
votes
3answers
149 views
Storing Phone Numbers - Proper design
First off, I'm not a DBA, I'm a software engineer and have been building applications which are database backed for my entire career. One of the things I remember (maybe incorrectly) is that when ...
1
vote
1answer
155 views
DB stored procedures: how do they help increase security in web systems?
The usage of Oracle PL/SQL procedures for controlling data access is often emphasized in PL/SQL books and other sources as being more secure approach. I'v seen several systems where all business logic ...
1
vote
3answers
237 views
Many-to-one Subselection in single query
I have two tables with a foreign key from T1->T2, in a one-to-many relationship. That is, 1 tuple in table T1 is associated with 0..N tuples in T2.
To create a simple example, lets say T1 is Cars, ...
0
votes
0answers
36 views
Designing Mysql Database [closed]
What should I bear in mind at the time of design mysql database ?? I know the basics. But I would like to know What do you do when you design a database. Which ways are you following??
Thanks
0
votes
2answers
66 views
what is the best choice from those two tables?
what is the best choice from those tables??
table A : keep DB simple with lower number of tables ... but i will find difficulties if i wanted to extract certain data from column of doses coz it's ...
1
vote
1answer
100 views
a question in tables relations
Table A is linked to Table B , and Table B is linked to Table C using primary and foreign keys
My questions:
Should i link Table A to Table C too?? Or Table B can act as intermediate link between ...
3
votes
1answer
104 views
Help to create indexes to improve query performance and reduce report generation time
The database in SQL Server 2008 has these tables below - the main table being the Master:
Master
A_ID (PK)
SYSSerialNumber
Bootlog
SequenceNumber
FileName
Cfg
Unique(serialnumber,sequencenumber)
...
0
votes
1answer
91 views
What MySQL storage engine and table schema should I use?
I am making a web project and I created necessary and the most important table, but I am not sure that I did it right.
In this site users will post data like text, picture, text's category, etc. I ...
2
votes
2answers
156 views
Please review my first thought-out attempt at a database schema
I am researching database design and theory and have finally made a thoughtful attempt at creating a schema that would be based upon SQL. I would like, if someone has time, to review my layout and ...
0
votes
0answers
47 views
How to store massive quantities of machine generated data?
I am tasked with developing a system that stores massive quantities of machine generated data (syslogs) from a number of server farms and I am wondering what tools you fine folks use such instances. ...
3
votes
2answers
208 views
Save history editable data RDBMS
I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means ...
1
vote
1answer
151 views
Does my design adhere to 3NF?
I created this schema with OpenOffice.
Does this design adhere to 3NF ?
(A relationship link between Equipment and Supplier_Equipment , Customer and Membership should be created but OpenOffice ...
1
vote
1answer
557 views
Derive Attributes in Tables?
I have designed my ERD. It has derived attributes. When it comes to relational data modeling using relational schema, I've designed my tables and linked everything properly. I don't know if I should ...
4
votes
3answers
146 views
Storing date increments in tables
I'm working on a ASP/MS SQL project that performs a lot of calculations based on dates and time. In one case, the system will lookup a value based on the nearest half hour increment throughout the ...
2
votes
3answers
179 views
Empty LONGTEXT or NULL?
In my MySQL DB I have one field called html_contents that contains all the html to be shown in a webpage. Obviously the html could be huge, and certaintly bigger than 64KB, therefore I decided to use ...
4
votes
1answer
129 views
Writing a database analysis document
I've been tasked with writing an analysis of a database that is currently used on a small scale. The intent of the document is to show to the business (as well as technically) that the database can ...
1
vote
1answer
68 views
Tables of a huge database
When I work on a huge database, and after following normalization rules, I found that I have many tables for each part of my huge database. That makes me worry about the final number of tables, it ...
2
votes
2answers
181 views
How to properly design outgoing/incoming mailserver configuration per user?
I'd like to store incoming and outcoming mailserver configuration per user, it's not a real live project, but to learn database-design (never done before).
Let's say I have a ...
-1
votes
1answer
69 views
Can I make that connection?
If I have a column with data type varchar(max) in a table that contains a big piece of text, can I make a connection between a word or a sentence in that text with another word or a sentence in ...
0
votes
1answer
104 views
Patients profiles database
I am making software for patients in hospitals. I want to make an option that if a doctor has prescribed a drug that interacts with another drug that the patient already uses, a warning will appear ...
0
votes
1answer
63 views
How to design this kind of relations in a database
I have a conceptual problem and I would like to get your ideas on how I'll be able to do what I am aiming.
My goal is to create a database with information of persons who work at a place depending on ...
3
votes
1answer
163 views
Applying user-defined fields to arbitrary entities in a SQL Server database
Currently we have an old (rather crude) system that has user defined fields, which are mapped against rows in arbitrary tables. This was an after-the-fact modification based on a customer request, and ...
3
votes
2answers
300 views
How should I best design the tables and relationships, given the following rules?
In a Pawnshop business, customers pawn, sell or buy items. A contract which specifies the customers information, the items, and the terms and conditions is created whenever customers pawn, sell or buy ...
4
votes
1answer
90 views
Set the primary key for many to many relationship
For the two relations, Customer and Equipment:
There is a limited number of equipments (stock) and upon hiring I need to store the date of hire and the expected return date.
I sketched this:
...
3
votes
2answers
107 views
Finding violations of the symmetry constraint
Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up ...
0
votes
0answers
25 views
How do I represent in UML a change of field name from one table as primary key to another as the foreign key? [closed]
I know I can simply write it up in notes, but is there another, perhaps more approved, method?
0
votes
1answer
83 views
Is it acceptable to have a surrogate/primary key of one table be the primary/foreign key of another table?
For my purposes I'm trying to apply this to Microsoft SQL, but would like to know if there are restrictions for other DBMS regarding this.
I don't know how to represent UML as ascii characters so if ...
1
vote
3answers
90 views
Storing summed values
I'm trying to create a database to store values for Profit & Loss statements and I'm wondering what the best way to structure this would be. Specifically, amounts that are are equated from other ...
1
vote
1answer
211 views
Are these database tables normalized?
I am developing a webapp for an institute and I designed these tables:
Are these tables normalized?
0
votes
1answer
54 views
Several variants of a string in tables
I need:
a table containing keyword phrases;
a table containing URLs;
a table (or maybe a view) with keyword-URL pairs (referring to IDs of the two previous tables);
tables holding keywords/URLs for ...
7
votes
2answers
521 views
Is a composite index also good for queries on the first field?
Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index?
...
