New answers tagged database-design
1
Relational databases are not built to handle this situation perfectly. You have to decide what is most important to you and then make your trade-offs. You have several goals:
Maintain third normal form
Maintain referential integrity
Maintain the constraint that each account belongs to either a corporation or a natural person.
Preserve the ability to ...
1
If it's a table of 3 columns, you're probably overthinking this. Strict 3NF is all well and good, but when you're talking about possibly having two tables just to follow it precisely, it's time to step away from the table, nobody's getting hurt here...
The intent of 3NF is that each table should contain unique keys, and all of the columns should describe ...
0
Can you post the table definition (desc ) and the current indexes you are using ?
Because you are currently working on (as in still developing ?) on an ad platform, don't try to over think the solution. Check "OpenX" (open source ad server) and get an ideea on how others solved the problems you're facing now.
"each advertiser will have their own table where ...
0
Try this
userMaster
| USERID | NAME |
-------------------
| 1 | Peter |
| 2 | George |
machineMaster
| MACHINEID | MACHINENAME | NOOFSET | IMAGEURL |
--------------------------------------------------
| 1 | treadmill | 10 | blahblah |
| 2 | leg extension | 5 | xyz.jpg |
| 3 | xyz | ...
0
Track your achievements in parts. Right now you have some indicative (i.e. descriptive) information and one "business rule" column (Metric).
You actually need more than one business rule column. Depending on what you foresee being your needs and how much work you want to put into now, this might be either one or two more business rule columns, or possibly ...
0
Pagination has be properly planned in two ways
Indexing
Having the best possible index helps to organize keys whose columns are like these:
datetime
advertid,datetime
Query
The query should be created in such a way that it is a JOIN of two things:
The Base Table
Subquery on Base Table Where
columns are a certain value
datetime range is given
define ...
0
Triggers are problematic because they tend to get forgotten about. I only ever use triggers for auditing purposes such as updating a "last updated datetime" column. Stored procedures and functions can be used at any time since they are called directly by processes wishing to modify data -- as opposed to triggers that are fired as the result of data being ...
0
Most questions and answers assume some sort of "binary tree indexes", but there are other sorts of indexes, perhaps most notably a bitmap index -- which as its name suggests is a bitmap per value with one bit per row to indicate (in)equality with the value. In comparison to tables and binary indexes, bitmap indexes require very little storage and are ...
0
Depends on all the WHERE, JOIN, GROUP BY, ORDER BY (etc.) clauses in your query, and how well the DBMS query optimizer can use each of the indexes to satisfy them. It could use one index or all or anything in between, and different DBMSes will often make different choices.
It is even possible no index will be used even though it could theoretically satisfy ...
3
It depends very much on the DBMS being used. Some can never user more than one index per table and query other can do so.
But if there are multiple indexes sharing the same column, I doubt that even the DBMS that can use more than one index would do it, especially if the leading columns are identical.
If there is an index on (foo) and one on (foo, bar) ...
3
This is no single answer
The optimiser will choose an index that best suits the predicates of the query.
This depends on
statistics/selectivity
index key order and includes/covering
number of predicates
You may also have index intersection or key lookups where 2 or more indexes are used
1
I don't have any insider knowledge of how Google Docs is implemented. However, if you look at things like Windows file permissions, this type of thing usually works on the these principles:
Inheritance - To grant access to many objects with the same scope, grant the access at the highest level (e.g. folder, not file) and all children of the granted node ...
0
You need three more constraints on each of your linkage tables. The first constraint says the the two FKs, taken together, are unique. The other two constraints say that neither of the FKs can be NULL.
If your linkage tables had declared a composite PK consisting of the two FKs, you would have gotten the same effect with just one constraint. The Id on ...
0
Space savings are probably the least important reason for normalization; flexibility of the data model, data integrity, and ease of querying those data would come before that.
What you are trying to do here is often called "premature optimization". You probably don't know yet if access to the user profile will be your performance bottleneck. I'm willing to ...
5
I would lean toward multiple databases. Primarily because you can set them up for different recovery, SLA, maintenance, deployment, changes, etc. And also this allows you to move certain databases to faster or larger I/O on different drives without disrupting everyone.
For the master tables that are used by all the applications, no I don't think you should ...
1
If you don't want to go for a full EAV solution, you could try something like this:
product_base
------------
id
product_group_id
(other fields)
product_option_types
--------------------
id
description
product_options
---------------
id
product_id (fk to product_base.id)
product_option_type_id (fk to product_option_types.id)
...
0
There is a class of problems for which the empty string is useful, and carries meaning that NULL specifically does not.
The empty string is the identity under the concatenate operator. If you concatenate the empty string with any string you get the same string back. If your application manipulates strings in this way, then reserving the empty string for a ...
1
You can do swapsies by specifying both tables in your RENAME statement.
CREATE TABLE new_table (...);
RENAME TABLE old_table TO backup_table, new_table TO old_table;
MySQL documentation on RENAME TABLE says:
The rename operation is done atomically, which means that no other session can access any of the tables while the rename is running. For ...
0
It is better for music, books and ... Consider a table. And use of natural interfaces, such as user-music table(UserId,MusicId,UserMusicId). It will remain empty fields often move to another intermediate tables.
Fields containing basic information of user insert in user main table and For more information, Favorites, and ... use of Interface Tables for ...
1
Your design is correct. Tables with many to many relationship needs to have an interface table. Interfaces table contains foreign keys to the main table, it must have a primary key. Good luck
0
It depends on the purpose of the key. "Surrogate key" can mean different things to different people; to my mind, it means a candidate key (a field or combination of fields which uniquely identify a record) other than the primary key. For example, social security number may be a surrogate key for customers (not a very good one, mind).
If the values are ...
0
A table can and should have as many keys as it needs. Usually when a surrogate key is used it means you will also want some alternative key as well (variously called a domain key, natural key or business key).
The practice of designating any one key as "primary" is of no great significance. It is simply a convention to mark one of the keys as "preferred" or ...
1
A coworker was suggesting that it is correct for a table to store 'description' and 'notes' as a foreign key rather than in the table for performance reasons, given that some of the records will contain NULL (or empty string in this case), and presumably will be split across pages.
Possibly, depending on RDBMS, the exact data types, and what the ...
2
With the clarifications in your comments, that there are only 2 levels of products, e.g. only Products and SubProducts and no subproduct is related to two or more products, your design is fine.
I would only add two unique constraints on table SubProducts, on (IDproduct, IDsubProduct) and on (IDproduct, Name) - or make one of them PRIMARY KEY and the other ...
0
If you have notes in a separate table, you can have multiple notes per thing; there's no need to be restricted to a 1:1 relationship (unless that is a requirement). For example:
Foo
---
id
(other fields)
Foo_notes
---------
foo_id
note_id
notes
-----
note_id
note_date_time
note_priority
note_author
note_content
If you are really only ...
0
A hierarchical relationship is probably fine:
PRODUCT
id
name
parent_id (nullable)
insert into product values (1, 'Proc A');
insert into product values (2, 'SubProA1', 1);
For performance reasons, you will need special queries. I'd use postgres over mysql if I were you. Use WITH queries.
4
I think the right answer here depends a lot on your application, and how important those documents are.
For a document management system, or a system where recoverability of the stored documents is critical (so most things financial, HR or CRM related), storing documents inline or using your favourite DB vendor's proprietary document tech seems like the ...
0
You may find this question of value: http://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete
Highlights:
Your tables should be set up with foreign keys with DELETE CASCADE.
Example:
CREATE TABLE categories (
id int unsigned not null primary key,
name varchar(255) default null
);
CREATE TABLE products (
id ...
5
Instead of searching for tips and tricks (deferred constraints included) I would suggest that you simply design your way out of this "reference lock" -- so try something like this:
Facts
User(UserID) exists.
Address(AddressID) was created by User(UserID).
Address(AddressID) was created on Date(DateCreated).
Address(AddressID) was last modified by ...
6
Based on the sample table in your link, I'd suggest it's taking issue with fields such as:
phone1
phone2
or
notes1
notes2
notes3
This may indicate a design flaw (but not always), and if so, one that can probably be corrected by more descriptive field names or perhaps a child table.
For example, someone might have more than one phone number... what if ...
1
Neither of the other 2 answers are correct.
John could live at 555 Main Street, and his wife, Shirley, could work there. It's a home and work address!
This is the correct schema:
PARTY
id
name
MAILING_ADDRESS
id
suiteOrApartment
streetAddress
city
postalCode
PARTY_MAILING_ADDRESS
partyId
addressId
purpose {work, home, ...}
You could include a ...
1
I think that the best for your tag and posttag tables is:
ok for the tagid as a primary key
add an index on your tag field (this cover your second query)
add two index on posttag table like INDEX(postid,tagid) and INDEX(tagid,postid) (this cover your first and second query)
So:
CREATE TABLE `posts` (
`postid` mediumint(9) NOT NULL AUTO_INCREMENT,
...
1
At the end of the day, #3 is still the BEST option.
We go with what we think is simple at that point but more often than not, business will come up with another reason to add one more type of address.
Design it correctly from the get-go! Good luck!
4
If you are really sure that those types don't change (or at least not often) you can also use a variation of 3) using a column AddressType of type varchar with a check constraint that limits the values to 'work' and 'home'.
This is less flexible than a lookup table, but still better than a boolean that has some implicit meaning when set to false. And you ...
3
I'd go for option 1. With this approach there are no "hacks" in the app for anonymous users. The standard security model continues to apply with the only difference being that if you don't know who the user is (ie, they haven't authenticated) that you pretend they are the anonymous user, which in effect they are.
Option 1 is also the approach used by most ...
5
You don't have a choice but to create the cyclic dependency in 2 operations as below because one table does not exist when you create the first one.
CREATE TABLE A (A_ID INT PRIMARY KEY, B_FK INT);
CREATE TABLE B (B_ID INT PRIMARY KEY, A_FK INT REFERENCES A(A_ID));
ALTER TABLE A ADD B_FK INT;
If you wish to avoid cyclic dependency. Then you need to ...
1
Take a look at the Party Data Model, it should meet your requirements.
http://www.tdan.com/view-articles/5014/
2
Some insights from having actually built a couple of apps with contacts and companies.
Firstly, you're missing several use-cases in your outline. Among the colorful ones I've run into over the years which you don't necessarily cover:
Some organizations have sub-organizations, be them divisions, subsidiaries, you name it.
Some people belong to several ...
0
Man, I wish database books would cover the Party Model in the first chapter.
If your time is worth much to you, read Enterprise Model Patterns by Hay or Data Model Resource Book by Silverston. Can find em cheap on Safari.
Also read up on Table Inheritance:
http://martinfowler.com/eaaCatalog/singleTableInheritance.html
If you don't like nulls then use ...
4
The question I would ask is whether the direct relationship between Anthology and Composer is "important" to the system? There are all kinds of incidental relationships between tangible things that are recorded in any system. However, only certain of these are important for the purposes of the system itself. These are the ones that belong in a relational ...
0
read this:
"Multi-Tenant Data Architecture"
http://msdn.microsoft.com/en-us/library/aa479086.aspx
I would go with one schema per school.
If you used Hibernate (maybe NHibernate) you can automate the DDL generation across schemas:
"Multi-tenancy in Hibernate"
http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch16.html
2
Creating independent databases will create the risk that as data structures change with the application, structural changes (and any associated data migrations) must be propagated to all of the databases. This can get tricky. A school_id in the tables that need it will simplify things in the sense that you only need to maintain one database.
Multiple ...
0
There aren't many issues beyond what you have now that I can think of off-hand (I'm sure someone will correct me though!). Since you are proposing to have your audit DB on the same server, and, I presume, instance, then by using three part naming you are all set. You've already noted the unlikely possibility of someone removing the audit DB and likewise ...
5
You've missed one place to get an overview of Oracle: the Concepts Guide. It covers all the major topics (including backup and recovery, which is quite important and doesn't appear in the list of links you've posted).
Whats the next step? Create the Schema or Tablespace?
Both! They're orthogonal. Users are logical entities that access your database. ...
1
First of all, mysql (and other sql databases) are RDMS meaning that they are based in the relational model. This means that the design should be about entities and their relations. In your case:
Entities: locations, types.
Relation: one location can be of one type (if I have understand you correctly). This is a one-to-one relation.
The best way to store ...
5
The solution you outlined is one valid option - assuming that an item can only belong to a single person at any given time.
In PostgreSQL you can enforce mutual exclusion between the two fk columns with a simple CHECK constraint:
either a Parent or Child must exists
... you can add a simple CHECK constraint:
CHECK (a IS NOT NULL OR b IS NOT NULL)
...
3
This is a sample matrix.
The query should find all the green cells, with value = F (as is TO BE FOUND :) ) ...
SELECT
x AS MATRIX_X,
y AS MATRIX_Y
FROM (
SELECT
a.x
,a.y
,a.processed
,(
SELECT
COUNT (processed)
...
2
I would go with option 2, for two main reasons:
Keeping the separate logic in separate tables makes each table's design relatively simple and effective.
Using UNION should not be a big deal.
For a good explanation on this perspective, take a look at this blog post: How the SQL UNION Affects Table Design.
Top 50 recent answers are included
