Tag Info

New answers tagged

0

Your Primary Key is a unique key and should be sufficient to find the records that you need. However, if you need a sequential number for other identifying reasons, then YES you should make is a separate unique value. I personally would skip the GUID key and just use the Integer, but that is a choice you will need to make. Getting a sequential set of ...


0

Usage of primary key for database alone is a best practice. Member number will refer only the particular table entry but primary key is used to interlink tables using joint queries by having same primary key in different tables.


1

No, there is no requirement for tables to be related to one-another. The fact that you do have two tables separate from the other thirteen would be a code smell and you should be sure that you have not forced two different business objectives into a single database. If the two really do support genuine use cases for the others then there's no problem. A ...


0

You design is basic and cover 1 user -> many items 1 item -> many comments 1 user -> many comments The question is would you like the user to be able to comment on the same item multiple of times? if yes, you do have a good design else we would need to work on it.


0

If you assume a 3NF design in the entity relationship diagram below: Recipes are stored in a Recipe table, a recipe's ingredients are stored in RecipeIngredients table, and the MyIngredients table contains an entry for each ingredient on hand. Your Next Best Ingredient(s) is found with the following query: SELECT Ingredients.IngredientID , ...


1

Your question implies that you're using a Kimball Star Schema approach. Option 1 implies a star schema while option 2 implies a more snowflaked design. Kimball argues against snowflaking your design. Therefore, go with option one. It will be easier for your report writers long term and newer in memory database technologies will take advantage of it. ...


0

I believe it's necessary to refer what kind of data your table is storing. Let's say that from C1-C10 you're keeping one group of data and it may be possible that the data in C1-C10 may be redundant (ie, it's possible that you may have the same row of data from C1-C10 throughout your Master Table). Hence, it's wasteful to have redundant data because you're ...


0

My 2 cents, although I am not nearly as experienced as many of the others on this site. If it is related to data integrity (either from a relationship standpoint OR a standardized formatting standpoint), then I firmly believe that it should be handled by the database. If it's simply for a nice presentation, then I think that should be handled by the ...


0

I would always recommend to create constraints to prevent the insertion of wrong values. I don't see anything bad on it. It is just a little bit more work for the DBA but apart from preventing the insertion of invalid values it can also warn when it was about to happen letting the developer to correct his mistake, else it would be possible to have an ...


1

There are two ways to do this. The fast, and the "academic". In pseudocode (change to fit your database) The fast/high performance way: CREATE TABLE Products ( ID INT , Name VARCHAR , PricePerKg DECIMAL NULL , PricePerMeter DECIMAL NULL ... etc... ) While this is not pure 5NF, it is FAST because all lookups of products can be done with a ...


1

I'd say that this is pretty standard stuff ; Create table employees (id int primary key, name varchar(100) -- Plus some additional columns ) Create table responsibilities (id int primary key, description varchar(100) ...


3

First off, you do not want to use char(50). Use varchar(50) or just text. Read more: Any downsides of using data type “text” for storing strings? Assuming the following rules: Basic slugs never end with a dash. Duplicate slugs are suffixed with a dash and a sequential number (-123). Note that all of the following methods are subject to a race ...


2

A general rule of thumb is that you will have a table for each type of thing that you are recording information about. Each instance of a thing will be a record in a table. It would probably* be a terrible idea to have one table per user. Have one table with USERS and one table with THINGS. In the THINGS table you will have a foreign key which is a ...


2

SUGGESTION #1 Based on the WHERE clause, I highly recommend a compound index ALTER TABLE mytable ADD INDEX (md5,created_at); That way, all parts of the WHERE clause are answered by the index. SUGGESTION #2 Since you are imposing a limit of 200, collect the 200 keys first, then join the keys SELECT B.* FROM ( SELECT id FROM mytable WHERE md5 = ...


1

Go for md5(15) because it has better cardinality and ref always beats range in performance. You wrote LIMIT 200 without ordering clause. Just heads up.


1

You need a one to many relationship between Book and Writers to Images. image_id should be a Primary Key just like writer_id and book_id providing a unique id of that specific image within your data. Than you can have book_id and writer_id fields in Images table which will hold which book or writer this image belongs to. Or another way would be to add an ...


0

What is it we are trying to get to here? Do you want to list out all the outfits (combinations of shirts, belts, and pants) a user can wear? Or just what they have worn for a particular day? Sorry, I'm not following the needs for the three relational tables (user_shirts, user_shirts_pants, user_shirts_pants_belts). I would just have one table for each item ...


0

@mukul shukla You have to maintain organization ID while login for different organization. You can track each organization for their different table structure means if an organization stores Name , Address, Phone and other do not require phone then phone field will not be visible in frontend and null will be saved in the backend. I hope you can maintain it. ...


0

Here is an example how to bring an unnormalized table into first normal form http://www.1keydata.com/database-normalization/first-normal-form-1nf.php


3

Let me start off by saying I am not a DBA, I am a developer by heart and I maintain and update our databases based on our needs. That being said, I had the same question for a few reasons. Null values make development more difficult and bug prone. Null values make queries, stored procedures, and views more complex and bug prone. Null values take ...


4

Based on your comment above, it sounds like when you said everything was stored in separate "databases", you actually meant to say "tables". I'm going to work with that assumption... I'd suggest a simple table that stores who's been invited to which event, and what their response was. invitations ----------- id (primary key for the table) user_ID ...


0

If possible I would avoid creating denormalized tables. Historically this led to manteinance problems for me, after all it is more tables to take into account. If the database engine supports it, first I would create indexes on the views with the performance problems and then you can force the execution plan to use the view's indexes insted of expanding the ...


3

What you are thinking about is relatively common and pre-processing the data can indeed help the performance of reports and web pages. The 'cost' to this is that the pre-processing takes time, so your denormalized tables (you can think of this as a mini-OLAP Data Warehouse) are never fully up-to-date. I do not consider that to be a big problem, but just ...


1

If you don't need to retain the data from the previous day when you update the data, you could do this without downtime: Day 1: insert data from API into Table1 Day 2+: create a new table with the same structure as Table1 create NewTable like Table1; insert API data into NewTable (note: app is still using Table1 at this point) switch out the ...


1

I don't think there is many-to-many relationship exists in this case as the Intern will always be associated with a single company. I would prefer to go with say a LOG (or InternCompanyHistory) table to save InternId, CompanyId and DateOfChange or any other attributes associated with change of company.


0

For a simple solution, you can add a nullable foreign key to an INVOICE table to your USER_REQUEST. If you want to know what needs invoicing, just select everything for a given user that has a NULL foreign key. When you create the invoice, set the foreign key to point to the new invoice. For a more complex, but more sophisticated solution, you may want to ...


1

If important attributes of Spray or/and Sweep are changing over the time, I'd rather introduce Spray_version and Sweep_version and make them immutable (every change in Spray or Sweep will create a copy in appropriate *_version table , so old values are never lost). Thus, you refer to *_version tables. However, in real world I found it's less expensive ...


0

Not discounting @gbn's suggestion (in a comment on the question) to possibly store this data somewhere other than in a RDBMS, I will say that if you do decide to go the RDBMS route, you are better off using a single table with a column per each "type" such that they can be strongly-typed. Or, you could use a single table with a single string field as all ...


0

The difference between your options 1 and 2 looks like the difference between Class Table Inheritance and Single Table Inheritance. These are two different ways of mimicking inheritance in table design. If you want to explore the trade offs between the two techniques, I suggest you look up Martin Fowler's treatment of the subject. You can see a summary ...


1

as it will have three table one table for Owner, Identity Auto-Increment by 1 will give OwnerId one table for gizmos, Identity Auto-Increment by 1 will give gizmosId One table for mapping the many to many relationship as OwnerId - FK gizmosId - FK and Other columns Now as the combination of OwnerId and gizmosId will make a row unique you can create ...


7

Please, don't store comma-separated lists in a single column. This is just a disaster waiting to happen. If these are separate facts, they should be stored separately. Table GroupMemberRoles GroupID FK UserID FK RoleID FK (PK on all three, with perhaps other constraints) Your queries (say, to find the admins of a certain group) should be of the ...


1

Assuming that the app will be treating these various shop-types in the same manner for some operations (operations that do not care what type of shop that it is), then my preferred approach is to use a subclass / inheritance model. I have detailed this out in a couple of other answers here: Don't know how to transform variable entity into relational ...


1

I would use a variation of your second approach: **Expense:** id | date | amount | category_fk | description | ------------------------------------------------------------ 1 | 2014-12-01 | 1000 | 1 | | 2 | 2014-12-01 | 120 | 2 | | 3 | 2014-12-02 | 12 | 3 | Dinner at ...


5

I think this can be done with an additional column (say Day) that will be allowed to hold only 2 possible values (1 and 2). Assuming a team cannot be in 2 or more stores in the same date, then you could have a UNIQUE constraint on (Team, Date) and another on (Cycle, Team, Store, Day). The constraints would be: UNIQUE CONSTRAINT uq_1 (Team, Date), ...


2

We've used Change Tracking to handle this. We've found it lightweight and easy to set up and administer. The largest system's moving tens of thousands of rows (from a table containing millions) per day without signs of stress. Of course your configuration will dictate your throughput. It's big cousin - Change Data Capute - will handle the case where you ...


0

The "best way" is a matter of opinion, but I've previously created separate logging tables on which I've built custom reports. The logging tables can be populated by the application or using triggers. If your application (app or stored procedures) does the logging you will have to identify and modify each and every bit of code that performs those updates ...


2

IMO, the balance sheet, income statement and cash flow tables can be the same. Here's my take on it: -- Companies CREATE TABLE company ( [id] int NOT NULL PRIMARY KEY, [name] varchar(255) NOT NULL ); -- "Balance sheet", "IFRS Income statement", etc CREATE TABLE statement ( [id] int NOT NULL PRIMARY KEY, [name] varchar(255) ...


0

Without more knowledge of the applciation domain, it is difficult to give a good answer. I would however usually prefer design #2, as the "artifacts" (Income statement, Balance sheet and cash flow) (i suppose) always belong to one specific company / financial statement. With design #1, multiple financial statements could relate to the same artifacts, which ...


1

Okay I figured it out. No Need for Delimiters because it is technically one line or one statement and only needs one semicolon. Also make sure to notice that one is for before an Insert and one before an Update. I believe that SET was also necessary. CREATE TRIGGER CREATE_BY_MENU_TR BEFORE INSERT ON MENU FOR EACH ROW SET NEW.Create_By = CURRENT_USER(); ...


0

I think the benefit of having a common table for blog, video, gallery, comment is that you will have unique id for each one of these, but that is really not needed. And It seems that all these share some common attributes, so we need to have common table to save those, but from application point of view they will be different and needs to be save in ...


3

Either of these models look reasonable. The NoticeMasterFile intersection table is not strictly needed since you can get the information through either model. The question is: How do you intend to use the NoticeMasterFile information? Perhaps this change would support some future plans that you have. Of course, the problem is that looking at a data model ...


1

It sounds like your categories are a case of the subtypes pattern. Accordingly, I've added the tag subtypes to your question. There are two design patterns that you may want to look into. The first one is called "Single Table Inheritance". This design involves collecting all the subtypes (or, if you prefer, subclasses) into one table, and leaving ...


4

Considering that MySQL is still a relation database management system an (RDBMS if you will), you should considering the following things: Your tables are defined by the data they contain and the relationships to other tables Your data should be as non-redundant as possible This last point leads to the process of normalization, which is a very important ...


0

This is perfect candidate for normalization. Given products table with product_id, design your table something similar to this. client_id date product_id prod_total prod_cost prod_paid misc info Join should only involve 2-3 tables, not 14. Let me know if you have any questions.


4

Let's give a practical example of when I have done this in the past. We have a database that contains all the data for a muli-user application; the database also has a table of users with their access rights. All of this data is normalised as expected. Then we have a request that the application remembers what windows a user had open and what they were ...


2

This is an interesting thread with some well thought out answers. Not being conversant with all the implications of storing and retrieving serialised objects I think it would be interesting to provide the answer I might give to a DBA team or development team: The key is to meet current and future requirements, and keep the solution as simple as possible so ...


3

A very important factor: Java serialization (one done that is enabled by implementing Serializable) is a very bad format in itself, so you shouldn't really use it for permanent object storage. Drawbacks of java serialization include: Data is not really readable from other languages. It is not very easy to maintain forward compatibility of serialized ...


9

There are situations where this kind of design is sensible, without you describing what your projects is about and how it is used, it's hard to say whether this is appropriate or not. Your DBA may hate you if you store BLOBs, but in many situations the only other alternative is to turn the tables into Entity-attribute-value, which gets even more hate from ...


5

I have done this before - its a useful technique in certain scenarios however depends on the serialization format used. If I do this I make sure that I use a serialization format that allows me to de-serialze older versions of my model (e.g. XML). I'd normally use this in scenarios where the data format would result in a complicated relational model which ...


28

It is not, in itself, a bad thing - at all. Arguing about "which is better" without a proper context (=exact requirements) is an exercise in futility. The part in bold is wrong. You can easily extend objects already serialized to add new fields and achieve full binary compatibility with the older objects. You can also simply create new classes instead of ...



Top 50 recent answers are included