Tag Info

New answers tagged

0

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


1

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


3

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


-2

Vertabelo has a feature called 'Subject areas'. Subject areas indentify the tables, views, references and notes contained within. The objects contained in the area are shown under the area name and you can find the table representation in different subject areas using the dropdown menu in the navigation tree. Check this: ...


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


1

if you are going count the tables in DB then query would be SELECT COUNT(*) from information_schema.tables WHERE table_type = 'base table'


2

SELECT COUNT(*) FROM information_schema.tables WHERE table_type = 'BASE TABLE' --counts all tables SELECT COUNT(*) FROM information_schema.referential_constraints --counts all FK relationships


2

is this what business process of yours? that must be document table : create table document ( `id` int unsigned auto_increment, `title` varchar(128) not null, primary key(id) ); block table create table block ( `id` int unsigned auto_increment, `id_document` int // foreign key to document primary key(id) ); ...


0

you are almost on the right track with a slight misunderstanding. But I have a lot of tables and I don't one to create a trigger on them one by one. This is not correct. You just have to create SERVER LEVEL / DATABASE LEVEL TRIGGER that will take care of the database events that occur on the server instance or database. You can even filter out the ...


0

Thanks to @PieterGeerkins I've started to take a look into double-entry bookkeeping more, which seems to have helped me resolve the original problem. Using one 'account_id' in 'chart_of_accounts' for the jackpot (instead of one 'account_id' for each jackpot time period) allows me to not worry about referencing 'account_id' from the table 'jackpots'. I'll ...


1

First, just to be certain we're covering our basis, this piece of code: EXP FILE = B.DMP OWNER(B) won't work. That's not a properly formatted EXP command. But assuming that you're using a properly formatted command, all that a user really needs is the EXP_FULL_DATABASE role, and they should be able to export any object in the database: SQL> create ...


1

Start with: Create a ChartOfAccounts table with the Account code as Primary Key. Add a Foreign Key constraint to ChartOfAccounts on all tables with an AccountCode field. Use an IsDebit field, not the numeric sign, to distinguish Debits from Credits and reserve negative signs for transction reversals (if used at all). This is necessary in order to generate ...



Top 50 recent answers are included