Tag Info

New answers tagged

4

It looks like you'll need to change the user's default database, as it appears this is currently set to DB1, which no longer exists. exec sp_defaultdb @loginame='user1', @defdb='DB2' OR ALTER LOGIN <login_name> WITH DEFAULT_DATABASE = <default_database>


1

No. You should combine receipts and purchases, because they are 1 to 1 correlated - there cant be more than one receipt for one paypal purchase, and vice versa. Same goes for Bank Purchases. Therefore ALL of these basically constitute "Purchases". What you call 'receipt' just seems to be the act of CONFIRMING the purchase, and nothing else. Withdrawals ...


0

Thanks to Erwin Brandstetter on stackoverflow, I have my response: Using joins: SELECT u.*, t.team_organization_id AS organization_id FROM users u JOIN teams_users tu ON tu_user_id = u.user_id; JOIN teams t ON t.team_id = tu.tu_team_id WHERE tu_user_id = $user_id; To get that automatically, I could create a VIEW encapsulating the ...


0

Yes, this can be done. In the open source community, it has been done for years. In most situations, they just prefixed the tables. I am not aware of a tool to simplify the process. You are aware that you introduce new challenges. Permissions. Queries are more complicated and may lose some performance. An alternative approach is to move to ...


0

I would commend alternative 1 to you. Just as having cells as columns a01, a02 etc. is poor normalisation so, too, is having measurements as m01, m02 and so on. In your example you have disguised this by calling them foo, bar and baz, but that is what they are. To address this what you call measurement whould more usefully be called Well: CREATE TABLE ...


0

Shouldn't be too difficult, the below assumes you are using SQL Server. My design would be to have a table called "CurrentLogOn", which would look like: LogOnID (PK - AutoIncrementKey) UserName IPAddress LogOnTime (DateTime, default to GETDATE()) LogOffTime (DateTime, no default) You would then want a second table called "HistoricalLogOn", which would ...


0

Looking at the data you posted, what would be the expected result from your point of view? The fact that users share the same desk/IP shouldn't be a problem as long as both users are reported while they're logged in. You could either use your current table design (a single table with Row_Id, update_time, IP and User) or an aggregated table with Row_Id, IP, ...


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 ...


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 ...


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 ...


0

Generate ERD with PHPMyAdmin is also a better option. PHPMyadmin added this functionality from version 3.4 detail step to generate ERD : http://goo.gl/0z3vFE You can refer to PHPMyAdmin documentation for more info: http://www.phpmyadmin.net/documentation/#pmadb


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 ...


0

Even though 10,000 rows are not a lot, I would go for a slightly different solution that will perform a lot better. Keep you master table the same: id, name. Create a new alias/search table: itemid, alias Create an index on the alias and a foreign key constraint on the itemid. Do not have a delimited list of aliases, rather have a row for each ...


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, ...


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, ...


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, ...


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

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 ...


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


0

In addition to what @jynus has written above - check out my answer to a similar question here (see reference to Celko). Furthermore, the MySQL ENUM type should be avoided for many reasons - 8 reasons as to why they are "evil" can be found here.


0

I recommend you to read about the dangers of the Entity-Attribute-Value pattern on this presentation by @Bill Karwin. One of the solutions is storing NoSQL-like data in a serialized BLOB, if you do not need to read and write individual properties. That is, storing a key (product) as the primary key, and the variable property-value pairs all together in a ...



Top 50 recent answers are included