Tag Info

Hot answers tagged

6

Always start by identifying your natural keys, in this case key. If these are too complex (too many columns) or not stable enough (changes to often), consider adding a surrogate key (such as the ID column you mention). The criteria I use for keys are: - unique - stable - irreducible - complexity - familiarity In many situations there will be a conflict ...


6

Your second option is more flexible, but I'm not sure why you're worried about "tons more tables". Usually this would be done with a single table: contact_types ------------- id (PK) name contact_details --------------- id contact_type_id (FK to contact_types.id) value organization_contacts --------------------- id (PK) contact_detail_id ...


4

There is more than one school of thought concerning the naming of columns or fields. One school of thought says that the name should express the intent of the data being conveyed even if that name is separated from the context where it is found. Thus, a name like post_date or another name like comment_date tells the reader not only that this is a date, ...


4

I personally like the second option best. This is a fairly common way to solve this problem. Consider adding a "default_contact_type_id" column to your organization table for quicker lookups and/or reporting. Option 3: For the sake of completeness, you could add an XML column to the organization table calles simply "contact_information". An XML blob can ...


2

My first IT job was in this area - basically I was involved in a "fiddle" on the part of my employer to land a big contract which involved convincing the client that we had a functioning and "intelligent" order picking system. It involved alcholic beverages and there are many complex rules about these (tax reasons). Anyway, I just wish that Open Source had ...


2

Date dimensions are pretty standard in a data warehouse, and are highly recommended by Kimball as most facts tie to a date. Typically, the key is an integer. It can be a meaningless surrogate key, or it can be a "smart" key where the integer is in the form yyyymmdd; e.g., the key for August 2, 2014 would be 20140802. Date dimensions provide a set of ...


2

Use liquibase. It supports diff, generating a db from scratch, patching a db, rolling back a db, and a bunch of other stuff. You used to have to write everything in XML with liquibase, but not anymore. You can write 99% of it in the SQL dialect of your choosing. Example: --liquibase formatted sql --changeset neil:1 create table contacts( contact_id ...


2

A very basic answer would be to let the developer know what exactly they're working with without needing to assign aliases in the event more than one table is being used. For example, if you're joining posts on comments (I'm not familiar with WordPress but just as an example), and the posts table as well as comments table have the columns title, author, ...


2

All current RDBMS' tables can have CONSTRAINTS on columns. These constraints are checked every time data is inserted into the table. It can also check data against other tables. We know that each Plate Type has certain number of Rows and Columns. We can enumerate all Rows and Columns for each Plate Type. So, when data is inserted, the DB can check if a ...


1

The solution is to add the group category column in the StoreGroups table and then add a unique constraint on (storeId, groupCategory). The table definitions would be like this: CREATE TABLE Groups ( groupID INT NOT NULL PRIMARY KEY , name VARCHAR(255) NOT NULL , groupCategory NOT NULL VARCHAR(30) , exprireDate DATE NULL , UNIQUE (groupCategory, ...


1

What finally solved my problem was: turning archive mode off disallowing connections disabling triggers and constraints truncating all tables performing imports ( attemp of creating existing objects fails but data is inserted ) re-enabling triggers and constraints turning archive mode back on allowing connections back


1

OK - so in your first year, all going well, you might be looking at 50GB for 1k organisations? That's certainly not "pie in the sky". What I'm going to propose is based on my own experiences and other people's opinions may vary depending on theirs. I worked for a company that had fewer organisations but more data, however I think that what worked there may ...



Only top voted, non community-wiki answers of a minimum length are eligible