Questions tagged [database-design]

The development of the conceptual schema and/or the logical model and/or the physical settings of a database.

Filter by
Sorted by
Tagged with
5
votes
3answers
3k views

How to select a subset of data from a large ER model?

I have a quite large ER model in my database and lots of data. Now I would like to take a subset of this data into a different database for testing purposes. The problem is how to get a subset from ...
2
votes
1answer
103 views

Is it dangerous to have duplicated entries in a table?

My table have many duplicated entries, is it dangerous if I don't fix this? My table is structured like this: Music_id | Music_title | Music_author | Music_link 1 Look at me now ...
8
votes
2answers
11k views

Database behind a Multilingual User Interface

This question is about an issue somewhat more complicated than the one which has already been addressed in these old questions, all of which are duplicates of one another: Suggestion for database ...
0
votes
1answer
329 views

What would be the best way to design a table in Mysql that handles read items in an RSS feed reader?

I'm working on an RSS feed reader, it is mostly functionnal. But after some time beta testing it, I noticed that its very slow running some queries on a table that handles all the items that have been ...
7
votes
1answer
226 views

Relational database: in-RAM partitioning? Theoretical structure discussion

I'm rewriting an MMORPG server engine using some rather esoteric elements (theoretically good but rarely used in practice), and having a bit of doubt. Some elements of this are “solid” — but the point ...
3
votes
2answers
206 views

Is there any better approach in designing this database?

CUSTOMER: CustomerID (PK) Name Email PAYMENT: PaymentID (PK) Amount Date PRODUCT: ProductID (PK) Type Name Price PURCHASE: CustomerID (FK) PaymentID (FK) ProductID (FK) Quantity Or should I ...
1
vote
3answers
338 views

One to one relationship or another approach?

I need some addition thoughts on how to approach a database design I'm working on. The issue is I have three related items... * Clan (has many games) * Game (belongs to a clan, has many teams) * ...
2
votes
3answers
2k 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
11k views

Primary Key on a View

While answering this question https://stackoverflow.com/questions/4668146/how-to-add-a-primary-key-on-a-oracle-view/8558346#8558346 A lot of questions popped in my mind. 1. Is it advisable to have ...
2
votes
2answers
7k views

database schema for a product with multiple categories and hierarchical categories

I need some opinion on whether I am going in the right direction with this. I am trying to build a e-commerce site (with MySQL backend) where a single product can belong to more one than category ...
10
votes
5answers
16k views

Importance of ER Diagrams

I am a student and I am developing several projects as a part of my academia. While developing the database for one of the projects, we came across a situation where we thought about whether ERD is ...
6
votes
1answer
3k views

How to keep long multiple of questions and answers in database?

I am planning my database design to have 3 sets of multiple questions for 3 types of different customers (at least 3 at the moment), each set of them contains approx. 100 questions and waiting for ...
5
votes
2answers
6k views

What Is The Point of a Primary Key?

Why do I need to have a primary key on my database for it to function correctly? In every tutorial I read, you need to make the id key the primary key. What does the primary key do differently than ...
2
votes
3answers
2k 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 ...
8
votes
3answers
845 views

Definition for “logical difference”?

I'm currently reading "SQL and Relational Theory" by C.J. Date. And although I'm quite far in the book, I've got a few basic questions. I want to know what the term "logical difference" means, The ...
1
vote
1answer
406 views

Foreign key to subtype or super types?

I'm using SQL Server and in this case i have the following tables: Restock: Foreign key to an R&D Staff member R&D_Staff: subtype from Staff Sales_Staff: subtype from Staff Shipping_Staff: ...
10
votes
3answers
5k views

Implementing a one to zero or one relationship in SQL

Let us say that I am designing a database for a scenario where there exists a one-to-zero-or-one (1-0..1) relationship. For example: There is a set of Users, and some Users may also be Customers. ...
2
votes
2answers
903 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
3answers
5k views

Sorting by DATETIME slower than by TIMESTAMP?

I have the a DATETIME key on which I sort the results and the query is running quite slow. Will it run faster if I change it to TIMESTAMP ? Because I noticed that ordering by numbered keys is done ...
3
votes
1answer
1k views

Do I have duplicate key indexes?

CREATE TABLE relations ( object_id BIGINT(20) NOT NULL PRIMARY KEY, object_term BIGINT(20) NOT NULL, UNIQUE KEY link(object_id, object_term), CONSTRAINT fk_term FOREIGN KEY(object_term) ...
4
votes
2answers
3k views

6th normal form, recomposition query, efficient implementation

I found this paper written by Hugh Darwen for avoiding nulls in my database: Link, It describes how to implement databases in the 6th normal form so you can avoid nulls. The logic is described in the ...
1
vote
2answers
240 views

Designing an Accumulating Snapshot with Repeated Groups, need advise

Situation I need to create a pie chart that has to be able to be represented at the following levels: District, Region, and School. At the student level it has to show the trend of the student over ...
3
votes
3answers
551 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
3k views

User system database design

I'm designing a database for a new project and I'm looking for some feedback on the design before I start to implement/test. It basically consists of users, events, user profiles and a messaging ...
1
vote
3answers
8k views

how to Export a document contain tables name, columns names and column description from SQL Server 2008?

I created a db in sql server 2008 and I wrote for some columns description to describe them, What I want now is to find a way to export these tables design including the description for each column ...
1
vote
1answer
854 views

Stock management - needs suggestions

I need to create a database which will have below modules: Items [Food items, stationary] Items Stock Management [stock in out] Customers Items Sale Management Issue: The system will have different ...
-1
votes
1answer
133 views

database normalization and optimization for postOffice DB

I am designing a web application for a post office. Performance in database is critical for them. I have designed the below structure for the post database, is there any flaw in my db structure which ...
2
votes
2answers
1k views

Logical schema development and migration tool for different physical schema available including triggers and stored procedures?

We are planning to support for our application another rdbms (oracle). At the moment we are design our database schema with the MySQL-Workbench. For every new database version we have schema er-...
3
votes
3answers
1k views

Handling attributes that are time-variant in a Datamart

I have looked through the entire list of sites, and this is I think the best match. This is not really about database administration, more like database design. Please excuse me and point me to the ...
4
votes
3answers
5k views

SQL table design for primary keys (best practice)

I have a User table that is made up of the the following columns: ID (Auto-generated GUID) UserName Password FirstName LastName Email AddressID (GUID) This is linked to the table Address (in a one-...
0
votes
1answer
103 views

What model design supports both data store backed and free form entries?

I have an application where users are tracking a workflow around producing a derivative work from an existing Book. The derivative work, we'll call "BookDerivative" must have a book that it relates to ...
0
votes
1answer
180 views

What are the first necessary actions needed to help secure a database's schema & data? [closed]

This smells like an open-ended, opinionated, wiki free-for-all, but I'm not trying to make this a battle of opinions. It seems like there should be a good checklist out there for all to agree on ...
5
votes
1answer
121 views

Possible pitfalls of condensing 2 SQL Server instances into 1

I currently have 2 SQL Server 2000 database instances (each running on a different physical server), each containing around 12 databases (not all are active). We are planning an upgrade to SQL Server ...
5
votes
1answer
2k views

Webapp & MySQL: Row Level Security

I am trying to emulate row level security on a webapp I am developing using MySQL. Using this method: Creating a database with the the required tables where the data pertaining to all the users will ...
4
votes
3answers
1k views

Single versus Multiple databases

I am working on a hospital management system using DB2 with pureXML. Is better to use a single database for all the data (patient, doctor details, appointments, history, medicines, etc) or to use ...
1
vote
2answers
2k views

serial field issue

Q: I use a serial as a datatype for my primary keys in my tables. I insert a lot of data then truncate these data one after one many times to test the data entry. My question is: Is there any ...
2
votes
1answer
471 views

In DB2, what are the alternative ways of evaluating an index's (or several indexes) benefit?

I'm trying to evaluate different approaches to analyzing the benefit of changing the set of indexes that are available. If I pick some representative queries from my workload, I can run EXPLAIN and ...
1
vote
1answer
141 views

Is maintaining a single database an issue when the underlying project becomes larger?

I have seen this related post but it was more focusing on the amount of consumers for the databases, which is not my only problem. I have a situation, where I am taking part of the design of a new ...
5
votes
2answers
2k views

Constraint many to many table between two child tables

New to this exchange site so I hope my question isn't inappropriate. Creating a somewhat normalized database I had one design problem I'm not sure how to handle efficiently. One table has a One-To-...
0
votes
3answers
2k views

How using many tables and JOINS affect performance vs. using limited number of tables and lesser JOINS?

I want to design a db schema concerning a schedule. I. I can use all fields in the same table, for example: id | shop_id | mo (varchar) | tu (varchar) | we | th | fr | sa | su 1 | 1 | 08:00-...
1
vote
2answers
1k views

Can we decompose a schema into BCNF when it is not even in 1NF

Given a schema r(A,B,C,D,E,F) and a set of functional dependencies A --> BCD BC --> DE B --> D D --> A As we can see the schema is not even in 1NF as can be read here, now the question is can we ...
1
vote
1answer
406 views

Design Tagging module in the database

I've a database system which we want to define tags for many tables into it. For example I've videos, photos, ... different entities that we wish to make them taggable. The tags could be anything. By ...
13
votes
4answers
24k views

One Big Database vs. Several Smaller Ones

We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g., ...
0
votes
1answer
679 views

Oracle: Extract Graphical Database Representation [duplicate]

Possible Duplicate: Oracle Creating ER Diagrams and Data Dictionaries How can I extract a graphical (schema) representation of an Oracle database (tables and their relationships; with colums and ...
3
votes
5answers
2k views

Pros and cons of having one table per entity vs. having a shared table with an extra column indicating the entity type

I am working on a database design and am wondering about the pros and cons of two setups. I am going to collect data about two types of entities, lets say cars and boats. The structure of the data ...
13
votes
3answers
18k views

Best way to design tournament database

I'm creating a webpage for placing bets on all matches of the upcoming Euro 2012 football tournament. Need some help deciding what approach to take for the knockout phase. I have created a mockup ...
1
vote
2answers
378 views

Best type to use in a composite key when one of the values varies in type

I am building a data store for the content of multiple blog sites which have been scraped. Each of these sites is going to have an entry in a Blog table BlogId Url ...
1
vote
2answers
951 views

Reporting Database Considerations

We have a very large database that we want to write almost real time. We want to take data and move it to another instance of the database on top of which will be a GUI and MI tool which will be able ...
1
vote
1answer
1k views

Does composite cluster index affect performance of seek/scan non-cluster index which is part of the composite?

I have bank transaction table with columns Division Brcode TrDate ProjectCode Accountnumber EffectiveDate DrCr Amount EndBalance In the other table the Brcode itself is unique and is assigned under ...
1
vote
1answer
963 views

Pros and Cons of having additional columns in join table of many to many relationship

I searched but did not find any discussions on this topic. Are there any pros and cons on creating many extra columns on joined table? Does it affect speed of data retrieval because we are joining 3 ...