unique constraint in mysql
Hi, is a unique constraint an index by default? If not, does a unique constraint has the same performance results as an indexed column when using it in the "select .. where" clause? thanks ...
database-design
SQL (access) - add a new field to a composite primary key
I have a table called table1 which has a composite primary key using 'ScreenId' and 'ActivityItemId'. I have added a new field 'ProductId' which has a NOT NULL constraint. I want to add ProductId to the composite primary key. I thought that this would work db.execute "ALTER TABLE table1 PRIMARY KEY (ScreenId, ActivityItemId, Produc...
Drawbacks to having (potentially) thousands of directories in a server instead of a database?
I'm trying to start using plain text files to store data on a server, rather than storing them all in a big MySQL database. The problem is that I would likely be generating thousands of folders and hundreds of thousands of files (if I ever have to scale). What are the problems with doing this? Does it get really slow? Is it about the sam...
Log usage of database tables, functions, stored procedures, views etc
I work on a system that is based on a SQL server database that has a lot of years of development on it. It is not huge in data volume (a few GB) but it has a lot of complexity (hundreds of tables, hundreds of stored procedures). I want to start by cleaning out the stuff that isn't used any more. We have a weekly/monthly/quarterly/annual...
Good and small open source database for teaching
I need a pet opensource database to learn the principle of database design, can you suggest one for me. ...
One table per every similar object?
I write an application for drawing shops. I have these classes in my system: shop, cart place, rack and bakery. They have this properties: shop: X, Y, name, width, height, type, address cart place: X, Y, name, width, length, type, capacity rack: X, Y, name, width, length, type, height, balance_limit bakery: X, Y, name, width, leng...
Designing mysql database tips & techniques, and some mysql questions?
Well, I am going to design a database structure, but I don't know where to start. Can you guys help me by answering the following questions? My database is going to store about hundreds of thousands rows/data and serving 1000 ++ users at the same time. It needs to be well designed. Design mysql database structure questions: Q1: I know ...
surrogate vs natural key: hard numbers on performance differences?
There's a healthy debate out there between surrogate and natural keys: SO Post 1 SO Post 2 My opinion, which seems to be in line with the majority (it's a slim majority), is that you should use surrogate keys unless a natural key is completely obvious and guaranteed not to change. Then you should enforce uniqueness on the natural key...
How many tables/sprocs/functions in a database is too many?
I'm interested in database refactoring. I deal with several databases that don't have a large amount of data, just a few GB with at most a few hundred thousand rows. However, they have hundreds -- sometimes many hundreds -- of tables, views, sprocs and functions. In some places a divide-and-rule strategy using schemas has been implemente...
Database design for values which are sometimes predefined and sometimes custom
In part of a website I am designing, there is a section that will have both predefined values and the option to specify custom values. As a simpler example, let's assume that the values are "Sports" and that the application has predefined sports, but that some users will want to enter their own since I can't predict everything that they...
Is splitting a large django app a good practice?
Hi SO, I have created a complex E-R diagram for a django site I'm developing. It maps to 11 tables in the database. The site has many features, so I would like to split it into multiple apps. The Django manual said that Django apps should be pluggable, but if I split the models into many apps, they would be dependant on each other. Is t...
SQL Database design question
I am grappling with a database design issue at the moment, I will present a simplified scenario of what is going on: Lets say I have four tables, Equipment, CurrentState, StateValue, StateType with the following schemas: Equipment ------------ Id (PK), Name CurrentState ------------ Id (PK), EquipmentId (FK) (IX), StateValueId (FK), ...
What would you do to avoid conflicting data in this database schema?
Hi, I'm working on a multi-user internet database-driven website with SQL Server 2008 / LinqToSQL / custom-made repositories as the DAL. I have run across a normalization problem which can lead to an inconsistent database state if exploited correctly and I am wondering how to deal with the problem. The problem: Several different compa...
Which of these two DB designs will lead to faster queries?
Which will be faster, what will the answer depend on: 1) Looking up a numerical id in a DB table A, and then querying another table B for all instances of that numerical ID. where table B is likely to be 100x larger. 2) Querying table B directly using for the string in question. Now that I write this I think intuitively solution 1 is ...
Pivot Table vs Parent_ID for 1-Many Relation
In a general one-to-many (parent-to-child) relationship, is there a significant efficiency difference between (a) putting parent_id in the child table and (b) using a pivot table of only parent_id, child_id? NOTE: Assume Oracle if necessary, otherwise answer using a general RDBMS. ...
Modeling One-to-Constant Relationship
Can a One-To-Constant Relationship be completely modeled in Oracle with constraints? In other words, the PARENT entity ALWAYS has EXACTLY n-CHILDREN of the child entity, and each child only has one parent. Consider n to be a database constant. ...
CREATE OR REPLACE VIEW sql error
Trying to update a table view using: CREATE OR REPLACE VIEW [vtable] AS SELECT * FROM Files_Table ORDER BY File The table is returning the old view, not the updated. Statement tested in the Sqlite database browser: Error message from database engine: near "OR": syntax error but didn't get this in the program? Any idea why i...
how to manage more than one site admin and users in database?
I have to design a database for a application which is having millions of users.Now that website will be having multiple administrator like HRadmin,SuperAdmin,SalesAdmin.I can only have one role as Admin. My concern is that if i handle all user related data in one table,means all users,whether admin or user,credentials will be stored in...
Best way to store and select multiple states/cities for a Company?
Hello, I have Company A. I need the ability to link this Company to multiple States, and each state can in turn be linked to multiple Cities. For example: Company A \ - State A |-City A |-City B \ |-City C - State B |-City A ...
Does aliasing tables names in large queries have noticeable impact on performance?
Does aliasing tables names in large queries have noticeable impact on performance? Aliasing: ... ... FROM table_1 T1 ... ... ... Is it significantly different for the various DB systems out there? ...