Tag Info

New answers tagged

0

Here is a quick steps for native mysql import and export: `mysqldump -u<root> -p<password> db_name > dump.sql mysql drop/create mysql db < dump.sql` The dump file from first server can be copied to server2 and dump can be restored.


1

From a purely performance standpoint, using the identity key as a clustered primary key will be most efficient. From there I would do a non clustered key with a unique constraint on datetime, patientid.


0

Your current method is not bad. However, if you are going to be adding more races later (bird, fish, etc.) then creating a separate table for each could be cumbersome. I would recommend something like the following: Animal < # Animal_ID, Breed_ID, other attributes > Breed < # Breed_ID, Race_ID > Race < # Race_ID > A breed, to my ...


0

As NoSQL databases typically do not support joins, they expect you to store the data in de-normalized form. (So, throw the codd normalization rule book away. Just kidding). In this case, the thrid table better have the values duplicated something like (orderid, ordertime, [itemnames], waitername, orderserviced yes/no, servicetime, ...). itemnames can be a ...


0

There are several methods you can use such as mysqldump folder copy enterprise backup for mysqldump use following command: mysqldump -uuser -ppassword --database your_db_name --routines --triggers --events --lock-all-tables >outputfile.sql and use following command at destination: mysql -uroot -ppassword<outputfile.sql. You can also use ...


2

This is likely to be a many to many relationship. create table drivers ( driver_id int primary key, ... ); create table trucks ( truck_id int primary key, ... ); create table drivers_trucks ( driver_id int references drivers(driver_id), truck_id int references trucks(truck_id), primary key (driver_id, truck_id) );


1

Swap the foreign key so that the Drivers table has id_truck. This way multiple drivers can be associated with the same truck.


1

Questions to ask could include: What fits well with your current skills? (That might make it 'easier' to use.) Is your focus on getting something working soon, or on developing new skills? What is the cost of your choices (money, time, effort, unfamiliarity, etc)? With your list you do emphasize cost as a factor and the database is not expected to be ...


0

Option A is much better as compare to Option B. If you use Option B then the complexity of the query will far more then as compared to Option A where you can get your result by using JOIN statements.


6

Option B is the worst choice: Why Because by keeping values in CSV format in a single column, you are violating first NF http://en.wikipedia.org/wiki/First_normal_form. Most critical drawback of violation of first NF is complex join conditions. You have to apply join using LIKE operators and worst wildcard options or using other string functions. ...


4

Option B is a bad choice and to answer you question on performace. Perfomance wise option A is better choice. Option A will have simple query where as option B need to run complex sql query. Option A offers better indexing options which in turn offers better performance. Option A is neat and clean design.


1

Zip codes should be stored as text, as some start with 0 (screwing up formatting/sorting) and there is no reason to do math on them. Also, if you want to store global postal codes, they often contain letters. Phone numbers are a maybe for text, especially if there might be extensions. Or you want to store numbers like 1-800-GOT-JUNK. There's no reason to do ...


4

Your colleague is correct that it is easier to simply not think about it and just store everything as a varchar. But this comes at a large cost in terms of space requirements, performance, flexibility in querying data, and most importantly, lack of data integrity. This is not just a one-time cost; it is paid repeatedly over the lifecycle of the ...


3

There are several situations in which it's better to represent numbers using some kind of numeric data-type. It's a little more efficient, but that's just the beginning. You get support for built-in arithmetic using SQL operators without performing type conversions at run time. Not only do type conversions slow things down, but they can result in numerous ...


4

Size is one consideration. An int can hold up to -2,147,483,648 in four bytes. A char will need 11 bytes to hold the same value. There are built-in functions to manipulate the various data types. DATEADD() and DATEDIFF() are two examples. This will not be possible with date-stored-as-text. Constantly CASTing back and forth will not make for efficient ...


0

There are different data types in sql to store the data. if the all data are same then varchar is good to store. but in the future, you need the operation on the data, you can't because of limited functionality. better to go with the data type of data.


1

First - stop using the phrase "Null value", it will just lead you astray. Instead, use the phrase "null marker" - a marker in a column indicating that the actual value in this column is either missing or inapplicable (but note that the marker does not say which of those options is actually the caseĀ¹). Now, imagine the following (where the database does not ...


1

I think if you look at your model and you consider the requirements that it doesn't handle (i.e. your questions) then you will find that you need to expand your model somewhat. Consider the following ERD: (Note I use the James Martin crows foot notation which is a little more compact than what you have used but should be pretty simple to understand. The ...


0

If the company owns a database and has no DBA, somebody has to be capable of auditing that database directly, without using an application program as a go-between. Either that or the company is at the mercy of whatever enterprise supplies them with their applications. That person could be an in-house member of the IT staff. It could be the company's ...


-3

One of the major purposes of a UNIQUE constraint is to prevent duplicate records. If one needs to have a table in which there can be multiple records where a value is "unknown", but no two records are allowed to have the same "known" value, then the unknown values should be assigned artificial unique identifiers before they are added to the table. There ...


1

This may not be technically accurate, but philosophically it helps me sleep at night... Like several other have said or alluded to, if you think of NULL as unknown, then you cannot determine whether one NULL value is in fact equal to another NULL value. Thinking of it this way, the expression NULL == NULL should evaluate to NULL, meaning unknown. A Unique ...


0

You need to design a webpage that will have a form as shown. Now on submit button click, you need use any server-side language like PHP/C#/Python...etc to convert those form data in a sql query. Something like INSERT INTO TABLE..... you can have some idea from these links PHP Form ASP.NET Form


0

It seems to me that you should be able to draw your ERD in the same way that you would for a regular relational database. Take a look at the wiki article for Object-Relational databases; there is a lovely example image on the page that might be of use. While the functionality of an object-relational database might be somewhat different than that of a ...


19

Why does it work this way? Because way back when, someone made a design decision without knowing or caring about what the standard says (after all, we do have all kinds of weird behaviors with NULLs, and can coerce different behavior at will). That decision dictated that, in this case, NULL = NULL. It wasn't a very smart decision. What they should have done ...


5

Correct. The implementation of a unique constraint or index in sql server allows one and only one NULL. Also correct that this technically doesn't fit with the definition of NULL but it's one of those things they did to make it more useful even though it isn't "technically" correct. Note a PRIMARY KEY (also a unique index) does not allow NULLs (of ...


1

I see one design possible with your reqirement as Book Table BookId PK WriterId -FK PublisherId -FK Title ..Other columns Editions Table EditionId - PK BookId - FK ..Other Columns Sets Table SetsId - PK EditionsId - FK VolumnName-- Like A, B(or 1,2) Now I see one problem with above design, Let say with new editions if publisher changed, then ...


1

I would say that an Edition is an entity that is related to a Book. You then have a one-to-many relationship between Books and Editions. (One book can have many editions.) and a many-to-one relationship between Editions and Publishers (many editions are published by one publisher). When I was building actual databases, back in the day, I always had ...


3

No, your case does not block on Oracle, nor should it. Concurrency is one the the major reasons to use an RDBMS instead of say, Excel. But just to make it interesting, if you insert the same value in both sessions, it will then block. Session #1 CREATE TABLE t1 (c1 NUMBER PRIMARY KEY); INSERT INTO t1 VALUES (1); -- do not commit transaction Session #2 ...


0

Head First SQL was very helpful for me in terms of SQL. I also found Beginning Database Design From Novice to Professional to be very useful for understanding the Relational Model. The website SQL Zoo is also very useful for learning SQL. To be honest I wouldn't get so many books just on SQL - it isn't that hard, and Head First SQL explains it very ...


0

It depends what information you want to store about the edition. You could add another table with an Edition column and a Book_ID foreign key. (e.g. an Edition has a Book) With sets, you could have another couple of tables. One with a Set_ID column and a Set column; and another table with a Set_ID column and a Book_ID column. For example - ...


2

Performance It depends A LOT on the actual data (are there many people having read a few books each, or a few people having read many books), skewing (are there some power-readers?) and the books which you query - like ypercube mentioned the bible. And this is before the optimizer really sets in and decides to completely rewrite your query because it ...


2

I found the course at Standford to be helpful - if you sign up, you can view past lectures on many of these topics. https://class.stanford.edu/courses/Engineering/db/2014_1/info


4

The relationship between normalization and performance is complicated. Sometimes the pursuit of normalization and the pursuit of high performance lead in the same direction, sometimes in opposite directions. You have to look at each case carefully. More importantly, there is more than one measure of "goodness" in a database. In addition to performance, ...


0

In my experience it varies from one company/situation to the next. As a dba I loved toad and sql developer because of the ease of use. However there are times where you're required to script things using the lowest common denominator. For example writing installation or upgrade scripts that may be delivered to a number of customers. Some may have ...


0

Yes, the same concept could be used. What you have done is re-implement table partition, but in user space. Most industrial-strength RDBMSs will have this built in. The provided functionality often includes additional abilities, such as efficiently adding and removing partitions at run time without applicaiton changes. By choosing to roll your own you ...


2

I personally don't see an advantage to separating them, especially given that you will probably have a primary key on the Orders table that the OrderItems table will need to reference. I normally handle this by having a nullable column for "closed" and/or "voided" that is stamped with now() on the appropriate event but left NULL until then. If you're ...


0

There is no performance difference between single base and number of bases, because "schema" is just the domain of authority. Grouped together or aparted, tables stored and processed in the same way. From the administrative point of view multiple bases are bit preferrable as far as you can dump the whole data for single client by DB name, not the pattern for ...


5

You'd need a many-to-many table that maps people to companies. Something like (I'm guessing at your table definitions) CREATE TABLE employment ( person_id INTEGER REFERENCES person ( person_id ), company_id INTEGER REFERENCES company ( company_id ), CONSTRAINT pk_employment PRIMARY KEY ( person_id, company_id ) );


-1

I don't think the two problems you state are equivalent. For faster logon with a table as you suggest would be using table partitioning and that would help by limiting disk I/O on the requisite tables. But you might be able to get equivalent results by using a good index.


0

No matter what database you are using, you shouldn't find any problem with it, UNLESS you are using the VIEWS to UPDATE\DELETE records (I know is possible in SQL SERVER). If you are doing that (update\delete on the view), you will get a constraint error once you try to violate the reference.


0

You can skip table name prefix in the status column, You can have all the tables with same column name and same datatype, that will make your naming uniform through out the tables in the database I have seen Database with 2000 of tables, and most of them have column status with same datatype. TINYINT in our case. Let say a query. SELECT ...


0

This could become confusing - but it's not difficult to make it work if you really wanted to. Say you had two tables, TASK and PROJECT. If both tables have fields called status and id, you can still access them without conflict by referring to them as either TASK.status or PROJECT.status. E.g: SELECT TASK.id, TASK.status, PROJECT.id FROM TASK, PROJECT WHERE ...


1

To answer your questions: Year number is part of a (three column) composite primary key. There will be one record per employee + year + type of leave (vacation/sick/...) Year number probably means the calendar year stored as an integer. der_leave_cumulative_days_taken means the total of leave actually taken, which would be derived by summing the days ...


0

The model which we have suggested is to maintain company and Employee records from your end wherein we have used customer_id column in both the tables will help you to maintain consolidated report for a particular company. But from your reply we came to know that you would like use Saas model and to give access to each company to manage their data's. In ...


3

Doing a insert .. select max() ... is a bad idea to begin with as it doesn't work correctly in a multi user environment. Plus it would get the sequence behind the serial column out of sync with the already generated values. What you are looking for is: insert into bar (bar_id) values (default); Another option would be explicitly call nextval() when ...


1

You can maintain these details in the single database with two different tables. For example, DBNAME : Your company name Table 1 : Company_details(This table gives the attributes of company) Company_Id Company_Name Company_contact Company_standard 1 Aaaa xxxxx MNC 2 Bbbb xxxxx MNC ...


0

I would reverse the naming convention for dealing with subset types. So a table of users, then another table of users_admin, users_reader, users_approver. I've used this in the past for parent-child tables. It makes them easy to locate and determine who is the parent and who is the child when looking at a list of table names.


1

I think it's better that timesheetEntry table should reference to job table as JobId will be PK of Job table and so associating timesheetEntry with job table will provide better referential integrity of data


1

When creating a data model a good place to start is to have an entity type for each noun in the description of your problem. The attributes of these entity types will be the values you wish to store about those nouns and verbs in the description will become relationships in your data model. In the database the entity types become tables, the attributes are ...



Top 50 recent answers are included