Tag Info

New answers tagged

0

If you put everything in one table, you will have a bigger, redundant table. If all the tables are properly indexed, the 3 tables solution will be fast, because a small number of rows will be read for each query.


0

The Answer by MDCCL is fascinating, educational, and presumably correct (though above my pay-grade). In contrast, I re-interpreted the Question and went back to basics for the simplest possible solution. Perhaps I am cheating and not truly answering the Question… but here goes anyways. I was confused when reading and re-reading the Question. When seeing ...


1

How about encrypting the vote using a key that only the user and/or the system knows?


2

I won't put all of them together. Even if you query them together, it may occur that your application will be able to post multiple images per post or multiple events per post or multiple posts per event. In this case you would be able to save space. The JSON may be a good idea, but it would be better (in view of indices) to use multiple tables. This way ...


0

You should have a table for each class of thing you want to offer a discount on. From your question that would be a table each for products, customers and stores. Most likely you will have these tables in your design already. Each will have a column of an appropriate type to hold the discount. You don't say what you will offer but a percentage discount ...


2

Your Question is much too broad to provide any specific table design suggestions. Just this, "Which one has Employee", could many any of dozens of things as mentioned in the Answer by Neil McGuigan: Person who first took customer inquiry phone call, the sales person who responded, the sales assistant who filled out Sales Order, the production manager who ...


1

MySQL has limits, such as 1017 columns in an InnoDB table (as of 5.6.9). If you are not filtering or sorting on most of the columns, put them in a JSON string and put that string in a TEXT (or maybe MEDIUMTEXT) column. A key-value store can lead to lots of JOINs. The limit is 61. I'm confused -- Do you have 1000 sets of 7000 parameters? Or what? If it ...


2

Price shouldn't be in your order table. A sales order hasMany line items. Price belongs to line item. As does delivery date. An invoice is a request for payment. An order can have many (or no) invoices, and an invoice can be for many orders. It's a many to many relationship. You don't really need invoices if you're doing pay-before-delivery (or don't do ...


1

This schema would make sense to me: tblCompany ( Company_ID PK Company_Name ) tblProduct ( Prod_No PK Company_ID FK_tblCompany_CompanyID Prod_Name Prod_Mark_No Prod_Catalogue Prod_Type Prod_Expiry_Date Prod_Verify_Date Prod_Comments ) You could have separate tables for product type and product catalogue to get to 3NF but that's a ...


0

There is no earth-shattering difference between a data model depicted in UML notation versus a data model depicted in any of the many common ER notations. They all depict the most important elements: entity types and relationships. Depending on which graphical convention you choose, there may be other features of the model which are conveyed, such as the ...


0

I think you answered your own question. E-R diagrams are meant to be problem solving diagrams for the sake of creating a database schema, whereas UML are supposed to model a completed db. I personally don't place a huge amount of value on ER diagrams, as I would personally do such brainstorming in my head, but I imagine many people would swear by them. As ...


1

since your date will grow rapidly. I would suggest to separate all the 'character field' into dimension and link with numeric values in the fact table which will only contain numeric data. This approach will be extremely fast and save you tone of space. also, depending on the partitioning configuration you can make it more efficient for retrial create ...


2

Hmm... I'm a bit confused. By definition, if two events happen at different places, then they're different events. My advice in this scenario is to tave 3 tables as follows: CREATE TABLE Event ( Event_ID INTEGER, Event_Description INTEGER, -- FK Event_Location INTEGER, -- FK Event_Date DATETIME ); And then two other "parent" tables CREATE ...


1

You say 100 measurements once a day. I'd have two tables as follows: CREATE TABLE Measurement ( Measurement_ID INTEGER PRIMARY KEY, System_ID INTEGER, -- FK into the System table Measurement_Date DATE (or DATETIME depending), Measurement_1 M1_Datatype, .. .. 100 lines Measurement_100 M100_Datatype ); CREATE TABLE System ( System_ID INTEGER ...


1

Of the two options mentioned, without any doubt, the first is better. MySQL is fine at handling large tables. There is no reason whatsoever to break the data into separate "equivalent" tables. In fact, having multiple tables with the same layout is usually an indication of a poor database design. For performance, you can then add indexes on the system ...


0

Social networks -- by definition -- involve real-time interaction between people. So traditional approach to federation and partitioning, that is storing all related data in the same partition and limiting the scope of transactions and JOINs to a single partition, only works in limited cases (e.g., a social network that only lets you interact directly with ...


0

You are far better off maintaining a foreign key constraint if your data warrants it. MySQL will automatically create an index - the FK must reference a UNIQUE KEY (obviously, can be PRIMARY - from here). See here for a host of reasons to as to why applying data constraints in the database is a VERY good idea. The index on the T table will increase ...


0

That's not how it works. Do it correctly first, then optimize. Use a foreign key if you need a foreign key. Then measure performance. Find the hot spots. Optimize those. If the foreign key happens to be large performance drag (doubtful) then remove it. Also, you didn't ask "What will be the impact of having incorrect data?"


10

Just a note: these new data types support the same sizes as the deprecated types they replace, e.g. 2GB of data (which means a different number of characters depending on Unicode and other factors). One thing for sure is you should parse all of your existing application code, stored procedures, functions etc. for instances of built-ins like UPDATETEXT, ...


1

We've gone through this without any issues. Anywhere you are updating or inserting data, make sure that is a traditional insert/update and that you aren't using WRITETEXT or UPDATETEXT. Other than that, everything should work fine.


4

It is great that you are taking the time to understand, classify and model the data you are dealing with since, from my personal experiencie, all this makes the whole development process easier and very flexible for future changes. And I am quite sure that you are also aware of this already. Preliminary data model and assumed business rules I defined a ...


0

Asssuming that I have understood the problem, the following picture describes (a part of) your database, in which an arrow from A to B corresponds to a foreign key from table Ato table B: Actually, there is a redundancy in that Grades are referred both by Groups and Subjects and each class must have the same grade if it is accessed through one of the two ...


0

One table, columns for subject and content, etc. Use InnoDB on 5.6 (or later) FULLTEXT(subject) -- for searching just that column FULLTEXT(content) -- for searching just that column FULLTEXT(subject, content) -- for searching both columns at the same time. However, see the limitations on what FULLTEXT can and cannot do.


0

There is no point to store the MP3 content in the database. This is not a matter of opinion. The reason is that the binary content of an MP3 is not relevant to index, which is what databases are for: for instance, you cannot write a query to return all MP3 files with the appearence of a saxophone. Therefore, you should do as the other answers suggest: only ...


6

As usual, it depends. Let's first consider what a key is, it is something that uniquely identifies something in your business. Even if you choose to use a surrogate key for some reason you should still identify and implement the natural candidate key's. A key ideally has a number of properties unique stable -- should not change frequently, not ...


3

A foreign key can reference a unique constraint, not necessarily only a primary key, so if you want to be a purist, include FinishedAt (and whatever else you need) in the parent table's unique constraint and use that to define the foreign key.


2

Joe Celko thinks very strongly that you should use the "real world" data such as a Name rather than using an artificial value such as an ID. However, many people here on DBA.SE prefer to use an ID value as the Primary Key. But it is a to some degree a philosophical question, or personal opinion. An aside: You did not say which brand of SQL (MS SQL ...


2

I'd use a surrogate product id. This avoids the need to store potentially lengthy strings multiple times across every order they are used in. Additionally if the product name is ever updated you only need to do this in one place. It is also more flexible as it allows you to have multiple different products (perhaps over time) with the same product name. ...


4

I think you are trying to blend together concepts from object modeling and concepts from data modeling in a way that isn't helping you to clarify your own understanding of the problem. I hope I can clear the clutter a little without too much rambling. The relational model, as such, does not support inheritance, never mind polymorphism. This means that a ...


0

With a 160 GB database, it does not sound like a 10 GB cache is overkill, but it depends to some extent on the size of the active data as well. I suggest that you look at Rolando's post: How large should be mysql innodb_buffer_pool_size? In the particular case he discusses, there was too much memory allocated for the cache. However, he offers considerable ...


1

* User - user_id auto integer - regtime datetime - username varchar - useremail varchar - userpass varchar * Question - question_id auto integer - question varchar - is_active enum(0,1) * Question_choices - choice_id auto integer - question_id integer - is_right_choice enum(0,1) - choice ...


3

All the information you need is in the catalog, have a look at the tables: information_schema.table_constraints information_schema.key_column_usage information_schema.constraint_column_usage However, if you want something more visual have a look at: http://schemaspy.sourceforge.net/ It will reverse engineer your schema and produce html output


1

You could try this or this for generating Entity Relationship Diagrams.


1

You could really use either option: one translation table with a FIELD_TYPE partitioning attribute, or a one translation table for each table requiring localization. The pros and cons of either approach are not likely to be related to the criteria you ask about: space or query performance. These aren't necessarily the most important criteria for you to be ...


1

Further to Ionic's answer, two good reasons for keeping languages as separate rows rather than separate columns are that: Your queries for retrieving localized strings are cleaner because the language selection is via a parameter to a where clause rather than using a dynamic SQL or complex branching conditions. Adding a new language to your localization ...


1

I know what you feel, I have 3 years working with MySQL, and in my case I always test the DML querys that can mod or broke any information on every table/database/Slave-replication. Its always the safest way to test your query before run it.


1

I notice that your specifications have turned a bit intricate (intentionally, right?), and I consider that this is a beneficial fact since this kind of scenarios will help you broaden your perspective on relational design. Regarding such situation, I am going to suggest you two similar methods to deal with these new conditions. In the first one I propose ...


3

This question is very similar with your other question. As already stated there, I would prefer a translation table like this: Table Translations Code (PK) (Your translation code) type (mobile, desktop, maybe something else later) language_id (your language code - 1:EN, 2:FR, 3:DE,...) value (your translation)


1

Well, I won't use a single table for this problem. If you need another language (just as example german), you'll need to extend your table by two columns. I would suggest a solution like this. Table Translations Code (PK) (Your translation code) type (PK) (mobile, desktop, maybe something else later) language_id (PK) (your language code - 1:EN, 2:FR, ...


0

You can reference both in your new table (another_table) and have a foreign key to both video_games and video_game_modes. Just make the column that references video_game_mode nullable. You can then put a constraint to force unique against video_game and video_game_mode if your intent is to have one row per video game and mode combination.


2

Bill Karwin covers the alternatives well in his presentation. I have a few notes on a similar but slightly different closure table in: http://dustbite.se/tree/ The idea is to have one table that represents the parent relation, and another that represents the ancestor relation. All changes to the ancestor relation is a consequence of a change in the ...


2

I would create a separate table for history. If it was me, I would use an auto-increment column for the primary key (historyID) , a column for the vial ID, and a column for a location ID. I would think the vial ID would be foreign key to the nodes table. You could then query against a vial to see it's full history. Since you don't list what your lookup ...


4

What you are describing is the EAV (Entity Attribute Value) model which most database professionals would run a mile from. It's also sarcastically called OTLT (One True Lookup Table) and is a classic novice mistake. Your hunch is correct! Here (and here) is the opinion of Joe Celko (a veteran SQL programmer who is/was a member of the SQL standards ...


1

Well the database design is ok. But I would suggest one change. Instead of making a direct join between Teamroster and Player, I would suggest this design: Table Player: id (PK) PlayerName ... Table Teams: id (PK) TeamName ... Table: PlayerTeamPositions team_id (PK) (FK references table teams) player_id (PK) (FK references table players) position_id ...


2

IMHO This is not good design. You have teamname in league table and teams table. What also is not good to have mixed conventions (plural and singular for table names) I would note that you should also put unique index on this fields: toplane, jungler, midlane, adc, support. create unique index teamroster_unique_idx on (toplane, jungler, midlane, adc, ...


7

My perspective about your list of questions If i want to add a column in Club that describes the owner, who will also be a member, what is the best approach without having the same member listed twice? If, as you stated in your specifications, a Person can be a member of only one Club, one option is adding a column called IsClubOwner to the Person ...


2

Here's how I would model the tables for that requirement (note the table and column names can be changed for whatever is appropriate for your application): In this model, the idea of a "Profile" and "User" is contained in the same entity, "User". The idea of a single vote for one of two user candidates is contained in the entity "Vote". If you want to ...


0

Once, when I was truly desperate for an ER diagram of an Oracle database, I used MS Access, and the relationship diagram tool inside it. I created table links from an Access DB to the Oracle tables, and defined the references inside Access. I then cranked up the relationship diagram, and moved the boxes around until it was at least presentable. This won't ...


0

I would highly suggest you look into table partitioning, as it will give all the benefits of creating separate tables for each version of your processed text, while also permitting concurrent access to the newest and any previous versions you wish. Assume you have some base data storage table: CREATE TABLE basedata (id numeric, orig_data text); Then you ...


1

I would add another table for versioning of data. It would have composite key of data and timestamp or version number and in table I would hold actual data. When new data arrives in table data is updated and in versioning table is inserted. Regarding vacuuming you set auto vacuum for that table. If table gets bloated despite auto vacuum setting you can run ...



Top 50 recent answers are included