New answers tagged

0

What you are describing is data migration. To do this successfully you need to understand both the old data and new schemas, business rules and the data itself. To migrate the data the most common option is to write conversion scripts which insert data into the new schema that respect the new schemas rules, referential integrity and business logic. If ...


0

If you lump all the column_## columns into a JSON field, you can have a limit of 4GB. The question of how much gets complicated. InnoDB has 4 ROW_FORMATs: REDUNDANT, COMPACT, DYNAMIC, COMPRESSED. I think you could have 400 columns of VARCHAR(255) or TEXT (or ...) in ROW_FORMAT=DYNAMIC. That format uses a 20-byte pointer in the record to where the actual ...


0

as well as inserting into the login_coordinates table I don't see the benefit of redundant storage. Just write to the table login_coordinates. Easy enough to access with only 10 rows per user. Don't update the user row as well. Basically, what @Ziggy already suggested, but with more flesh. Based on this table: CREATE TABLE login_coordinates ( ...


0

You could implement an ON INSERT trigger and delete, for the same user, any rows with a timestamp older than the oldest (MIN) of a subselect ordered by timestamp DESC limit 10.


1

I think it's over-prescriptive to say which of your two solutions is better. All you need is a unique constraint on {EmployeeID, AddressID}. Whether or not it's the actual primary key is up to you. It's perfectly fine to have a different primary key; single-field PKs do tend to work better with some ORMs, and there are benefits in simplicity to having all ...


6

The compound PK of EmployeeId and AddressTypeId is exactly what you want, and you don't need anything else. The relationship is still a one-to-many relationship, as for one employee, you may have many address whether it is a home address or a work address. The bridge table is interesting if you may have the same address for multiple employees AND that a ...


0

JSON documents are ideal for this purpose. MySQL can store JSON documents via its JSON datatype, and it has a variety of function for extracting information in such documents. However, MySQL doesn't currently have an effective native way of indexing JSON documents for fast search. Googling for "mysql json index" will render several strategies for doing so, ...


2

Error code 1064 : This means you have an error in your SQL syntax.( ; missed) Select Model, Variant, EngineSize, RegYear, Mileage, Price, Sector from sampledata ; update sampledata set RegYear = 2010 - RegYear where sector = 'Van Derived Cars' and mileage > 200 and regyear != -1 and price != 999999; Mysql error code 1175 : If you ...


0

First insert a row in a table of "objects" that includes an AUTO_INCREMENT PRIMARY KEY. Get the id by using LAST_INSERT_ID(). Then insert whatever rows you need into other tables. Use the id from the first step as the unique id. This works for MySQL and MariaDB. Other databases have the concept of a SEQUENCE. Such can be simulated in MySQL/MariaDB, ...


4

Create a supertype entity CarBody for subtypes Sedan, SUV, ... and move all general information to that supertype. Your M-M relationship will be between CarBody and Feature. See this topic for subtyping in datamodeling Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping .


2

The simplest and probably most common way would be to declare the (album_id, artist_id) pair of columns a unique composite key. There are two variations of this approach. First, you can keep the current structure of the album_artist table and simply add a unique constraint on the said two columns: ALTER TABLE album_artist ADD CONSTRAINT uq_album_artist ...


1

Normal forms are used to eliminate or at least reduce redundancy in data, as well as to eliminate the so called insertion/deletion anomalies. The BCNF eliminates redundancies and anomalies, but decomposing a relation in BCNF sometimes has the unpleasant effect of causing the loss of one or more functional dependencies during the process. For this reason ...


2

What you are looking for is a 0-many relationship. This is usually referenced by a third table. Publication_Scientists : Publication_Id (int) | Scientist_Id (int) with 1:1 relationships back to the other two tables. Preferably using something like an enforced foreign key.


0

On the JSON question... Look at the SELECTs you will be performing. If any of them need to search (WHERE) or sort (ORDER BY) on a particular field, then expose it as a column, probably with an INDEX. Otherwise, consider throwing it into a JSON column. At one extreme, all the info could be in a single table with (id, src_ip, dst_ip, timestamp, json). ...


3

The CASCADE clause works in the opposite direction. From the parent to the child table. When you delete a row in the parent table, the related rows in the child table are deleted as well.


2

If your attribute item_number is unique, you can leave it in your original table even in case it can have null values. In fact the PostgreSQL manual says: For the purpose of a unique constraint, null values are not considered equal. So this could be right solution: CREATE TABLE product ( sku text PRIMARY KEY, name text ...


8

1. Assuming that sku and item_number will always hold unique values I consider that you have come up to the answer by yourself, since an item_number is an optional attribute (or column at the implementation phase), i.e., it does not apply to all the product occurrences, therefore, from a logical point of view, it decidedly cannot (should not) be declared as ...


0

Your question is too broad for any answer here to fully address, so I'll just address one point. For the major query type of "Give me the latest data generated by a device", you could create a new column in the table that gets the same data as the "data generation time" column and then have a periodic procedure that nulls older values in that column for ...


0

when you define primary key on column sql server implicitly define cluster index on that column and indexes are faster when you use integer datatype,alphanumeric is little bit slow searching than integer column. production_order_table:- production_order int(pk) product_id int(fk) define non-cluster index Product_table:- product_id int(pk) product_code ...


3

De-normalisation and certain kinds of data duplication can be a convenient way to speed-up processes. Examples are caching, datawarehousing, and materialised views. Meanwhile the duplicated data is relied upon as a read-only snapshot (i.e. point-in-time copy of the actual data), or the system can guarantee consistency (like in the case of caches), it is ...


0

The only sure answer I can give to you is that you should be using a TEXT or BLOB datatype and not a varchar. Varchar leaves you with a chance that there wont be enough room for your data and your data can contain any type of information you require. Also (not really a problem) I would think you might be able to remove the recursive relationship in your ...


0

It looks to me as though child_1 and child_2 are two instances of a single entity type. If so, then you have a (second) relationship such that Every parent must have one and only one favourite child Every child may be the favourite of zero or more parents. The (first) relationship being A parent may have zero or more children (unless, of course ...


0

I went with the second structure posted in the question, based on the comment of @AakashM . It seemed to be a good solution though I wasn't 100% certain it would last, stay fast and wouldn't flood the tables with abundant data. I've been using it for a few weeks now and it seems to be handling itself just fine. Around 20.000 product records, around 10.000 ...


5

And I want to limit the number of referees on a single match to three, How can i do that? One method is with a trigger: CREATE TRIGGER tr_match_referee ON dbo.match_referee FOR INSERT, UPDATE AS IF EXISTS( SELECT 1 FROM inserted JOIN dbo.match_referee WITH (READCOMMITTEDLOCK) ON match_referee.id_match = inserted.id_match GROUP ...


1

First, find out if "mail" guarantees that all data is utf8 or ascii or whatever. If utf8, then use MEDIUMTEXT CHARACTER SET utf8mb4 for a limit of 16MB. For a 4GB limit, change to LONGTEXT. If the mail is not forced into some character encoding, then use MEDIUMBLOB or LONGBLOB (without a charset).


0

One approach you could take is to design a table that implements a stack into which can be placed the components of the logical expression in reverse-polish (postfix) notation. In such a design, the components of the predicate are each inserted into the table with an order number to specify the reverse evaluation order. To evaluate them a query is written ...


5

I see a tweet as an event happening, so I would model it as a new fact table FactTweet. More specifically as a factless fact. The dimensions for FactTweet would be DimDate, DimCar (if you can relate a tweet to a car), DimAuthor and I would probably keep URL and Description as degenerated dimensions. You could potentially add the sentiment of the tweet as ...


0

You can use variables for this kind of thing: select userId, count(*) from ( select @g:=case when userId=@lastuserId then @g else @g+1 end AS grp, @lastuserId:=userId AS userId from test1, (select@lastuserId:=0, @g:=0) vars order by transactionTime ) groups group by grp, userId order by grp


0

Upon realization that I had unwittingly reinvented a modified EAV model with Option #1 and would likely be headed into a world of hurt with this option, I decided to go with Option #2. Option #2 should preserve many of the benefits of RDBMS and I think MySQL/InnoDB can support the row sizes I currently expect. While I will have a quantity of NULL entries ...


1

I would consider adding 2 more tables. "Reviews" would contain the review columns itself, DateAdded, ExpiryDate etc, and a ReviewID. Then I'd have a second (junction) table ObjectReview which links buildings to Reviews, and would contain obj_id and ReviewID to tie reviews to buildings. One important point - it's not clear from your question - but you call ...


0

The following schema is what I came up as a fully denormalised set of tables. There are pros (e.g. flexibility) and cons (e.g. user interface complexity) to this approach, but you may find it useful, or it may shed some insights: create table arch_type_att_types ( attribute_type_id serial not null primary key, attribute_type varchar(100) not null, -- ...


2

I suspect the advice is predicated on the likely utility of partitioning a dimension table. In a data warehouse, fact tables are good example of the adage, big data is medium data, plus time. Dimension tables don't have time (not really), and as a rule don't have useful partitioning properties. Yours seems like a good example. Why is Accounts ...


0

You can solve this problem by either removing ProjectName from Inventory (since it is redundant because it is already specified in ProjectCategory), or enforce the relationship via a foreign key: CREATE UNIQUE INDEX ON ProjectCategory (ProjectCategoryID, ProjectName); ALTER TABLE Inventory ADD FOREIGN KEY (ProjectCategoryID, ProjectName) REFERENCES ...


4

There really is no "ideal" Fill Factor since there are too many other factors to consider. What is the actual page size of the table? How many variable length columns are in the table? If not "none, then: How likely / often are the variable length fields updated to a length greater than their initial value? If you have a table with a row size of 3000 ...


1

Considerations: How much disk space/memory can you afford? Lower fill factor will mean large indexes. Can you measure the current performance impact and adjust based on Page Life Expectancy and Page Splits? (A maintenance solution like Ola Hallengren's scripts will provide a table that logs rebuild operations) Keep in mind that even though the update ...


0

I would just go with a bigint with Identity column. Just remember to start it at the negative seed value since sql server does not support unsigned ints. Something like this... CREATE TABLE Records ( RecordID bigint IDENTITY(-9,223,372,036,854,775,808,1), RowLabel varchar(255) NOT NULL, CatLabelID INT, StartPosition varchar(255), EndPosition ...


0

In my opinion, this would vary depending on what your responsibility is for the database. I wouldn't want to expose my data structure to them as they might be able to take advantage through injection and the like. Additionally, it would allow them to form adhoc queries and reports which will most likely cause performance issues that you now need to support. ...


1

I had to use two JOINs here but I think that this will work. You can join on the same table multiple times SELECT a.t_code, a.t_waste_water, a.t_drinking_water, b.perim_name AS waste_water_perim, c.perim_name AS drinking_water_perim FROM schema.town a JOIN schema.perimeter b ON a.t_waste_water = b.perim_code JOIN schema.perimeter c ON ...


4

Managing an individual datum Assuming that —in your business domain— a user can have zero, one or more friends, and a friend must first be registered as a user, then each datum gathered in the multivalued column represents a single fact that has its own particular significance, entails explicit constraints and has the potential to be searched for and ...


1

If I understand your question then I don't think it is going to work like you expect (want) When you build up a query you can not use a variable (e.g. column value) as a table name for a join. This is not valid: select * from mainTable mt join mainTable.JoinTable as jt on jt.ID = mt.FK You will get an error mainTable.JoinTable not found (or maybe ...


1

If the data in each subset it truly independent and will never be reported upon together then modelling them as one table per entity type is perfectly valid. The fact that the table name will match a key in another table isn't something the database will care about - that table just becomes a list that tells the business logic what entity tables to expect to ...


2

Just looking at an example of a table is a wrong way of normalizing. You should understand the meaning of the fields to properly normalize. For instance you should know if the salary is the same for all the persons that have a certain role, or if it differs from person to person (in the above example we cannot know, since each role has only a person, so that ...


2

In addition to Ziggy answer, if you want to make this diagram work, you need to edit it, for example you should create a third table called cleint_order and this table has primary key from Client table and idproduct from product table since one client may have one or more product and one product can be ordered from many customers. useful link: ...


0

First in this case I suggest you to adopt a star schema. the transaction_data table obviously will be you reference to construct your fact table because it contains 'measures' like quantity,sales_value and other numeric attributes , the fact table also will contain substitution-keys of dimensions tables like Time, Product, Customer,demographic, etc.


2

It's better to extract some rarely used fields to another table. Especially if those fields are big (blob, long varchar etc). This will case smaller row size and better optimized buffer usage. Because small data (i.e 'id', 'name', 'status') will be stored in buffer and will have fast access to it. And queries with big fields (i.e. 'description TEXT') will be ...


0

I think it would be a good idea to create an 'Achievement' table with required condition to be unlock (ex:Win 8 rounds in a row) and an association table 'UserAchevement' to indicate users.the same To store every user progress (the number of Wining round for exemple) a 'Progress' table is needed.


1

UML class diagrams do not include relations in a class' attributes list, so when creating database tables representing those classes, the database designer must identify and specify them, much like the software developer must create the pointers and lists to support the related objects. In your diagram, Commande relates to Client but does not explicitly ...


1

You'll need to redefine your database to store that information. An example is the following: CREATE TABLE routes ( route_id int PRIMARY KEY, route_name varchar(200) ); CREATE TABLE stops ( stop_id int PRIMARY KEY, stop_name varchar(200) ); CREATE TABLE route_details ( route_id int, stop_number int, -- starting from 1 stop_id int, ...


0

I've had a look at this - am going to go back to it tomorrow, but I think you're going to have to think of every route as a number of sub-routes. Route A - F is also routes A - E, A - D.... D - E and E - F. BTW, I'm using PostgreSQL - D. Richard Hipp (the author of SQLite) has said that he uses PostgreSQL as a template for his own flavour of SQL, so it's ...


2

Background Apart from what Joel Brown's answer says, names are not one-to-many. They are a many-to-many relationship with a temporal dimension as well. A person may have multiple names simultaneously, all equal in status (that's indeed my case, as a dual national with different legal names in each country due to different cultural conventions) A person ...



Top 50 recent answers are included