New answers tagged

3

To answer your question directly: No. Using floats for a primary key is not a good idea. Why? Because floats can run into problems with rounding and precision (Is 1.20 the same as 1.2? Because mathematically it is.) Personally I would create a primary key on a single field. Preferably an integer. Integers are typically more efficient than floats and there ...


0

I think you approach is right. Formally it's mean you make common table for all activity. It's you abstract activity with common information set for all type of activity. Different tables for each type activity with information set linked to abstract activity table. So you can develop system for abstract activity work flow and for concert type of activity.


1

Splitting up the tables into different activities sounds like a much better plan than trying to shoe-horn 7 different activities into 1 table. Different objects belong in different tables - rule of thumb - better to have many smaller tables that are "thin" - i.e. fewer fields than 1 megatable which is "fat" (i.e. many fields, many of which will have to be ...


2

To be fair I know nothing of sqlite but treating it strictly as a design problem I would have <storeid1, storeid2, distance> With a primary key on a combination of the three. The table isn't going to change very often (only when new stores arrive) and the distance between two stores will never change so you don't have to worry about inserts a great ...


3

I wouldn't have the ContactInformation linked 1-to-1 to Person anyway; storing several addresses like that in ContactInformation violates normal form. Instead I'd have ContactInformation with its own PK, an FK for PersonID back to the Person table, and store as many Contacts as necessary for each Person. CREATE TABLE ContactInformation ( ContactID int ...


-1

Your already have it setup just create a new record in the "ContactInformation" table with the same PersonID and then the person has two addresses


3

Do not use (m,n) on the end of FLOAT or DOUBLE. That causes a rounding (at the bottom) or a truncation (at the top). If you want (m,n), you probably should use DECIMAL(m,n). FLOAT stores 24 significant bits of data (equivalent to about 7 decimal digits; storage=4 bytes), with an exponent ranging over about 10 ** +/-38. DOUBLE stores 53 bits (about 16 ...


1

I think the passwords would normally be kept in a separate table and encrypted. Also you might need a column for number of failed password attempts with a limit and something to reset the value every 30 minutes etc. I am guessing you would also have a stored procedure for the GUI to access passwords to prevent injection.


0

Yes, there is a difference. In the 1st design, an author-paper combination is related to one editor. So a paper can have many editors, as many as its authors. In the 2nd design, a paper is related to one editor. And all its authors are related - through the paper - to the same editor.


1

Floating point numbers are not always stored as you would wish, due to the way CPUs deal with floating point numbers. If you're always storing numbers that have 2 decimal points, store it as an integer and add the decimal point in the presentation layer.


0

The file handlers are not one for each collection or index but per connection and thread: https://docs.mongodb.com/manual/reference/ulimit/#resource-utilization So in my opinion you should definitely adjust your ulimit settings. See the Production Notes, too. https://docs.mongodb.com/manual/administration/production-notes/ I think a sharded storage of ...


1

Your question hits a 'gray area'; there is not an overwhelmingly 'right' way to design the schema. When the question is asked about city + state + country, I say: Have one Locations table that contains all 3 columns. And use country_code CHAR(2) CHARACTER SET ascii. I also say "normalize, but don't over-normalize". There are 'religious wars' fought over ...


0

Suggested max len First, I will mention some common strings that are always hex, or otherwise limited to ASCII. For these, you should specify CHARACTER SET ascii (latin1 is ok) so that it will not waste space: UUID CHAR(36) CHARACTER SET ascii -- or pack into BINARY(16) country_code CHAR(2) CHARACTER SET ascii ip_address CHAR(39) CHARACTER SET ascii -- or ...


2

I've used a program called Code Visual to Flowchart, if you feed in a SQL file it will generate a flowchart based on it. The free version is all I've used and it met the needs I had, making it much easier to work my way through a 3000 line procedure that was completely devoid of comments. "I can confirm I have no affiliation to this product"


2

If there is any possibilty of avoiding GUID as Primary Key / Unique Clustered Index, then take this option! A good (surrogate-) key should be: Narrow (NO) Unique (GUID is) Static (GUID is) Ever Increasing (NO) GUID (uniqueidentifier) needs 16 bytes - int (numbers up to 2^31 - 1) needs 4 bytes, bigint (numbers up ot 2^63 -1 which is far more than you ...


2

It depends. Using a normal UUID as your clustering key will result in greater fragmentation in the table and so waste disk space and memory. You can mitigate this mostly with NEWSEQUENTIALID if that fits your use of the table, or of course you could use some other key as the clustering key (specify NONCLUSTERED when defining the primary key and create ...


3

It will slow down inserts rather than queries. That's because it is essentially inserting a record somewhere randomly inside the existing records, instead of just whacking it on the end (as it would if you were using an identity or sequential dates). You can somewhat reduce this by using NEWSEQUENTIALID instead of NEWID (assuming the GUID is generated by ...


0

Personally I would split the design into several tables. I believe you need to draw the Entity Relationship Diagram to show which entities are involved in your database application. For example, customer account as one table, account transaction is another one, then for each transaction, you should define transaction details table to holder the break ...


0

It is OK to have many columns in one table. Indeed, this is the normal situation. It is not OK, however, to mix different types of information in a single table. Each table models a single class of "thing" in the real world. The rules of normalisation guide a database designer to separate classes from one another. Denormalisation (storing several things in ...


0

I would suggest using SQL Server Data Tools (SSDT), which is essentially the latest iteration of the Visual Studio Database Projects. I use this at places that do not already have a way to source control their databases since it easily integrates with TFS, which most Visual Studio shops use for source control. A couple pros I have for using it are: Source ...


0

1) Yes. It's a maintenance/documentation nightmare but technically there's no reason it wouldn't work. 2) In general each null will be one bit of storage. So 80 null fields might be 10 bytes per row. The full answer is that it varies depending on data type but with varchar for the most part it's a good rule of thumb. Some alternatives where you expect ...


0

question is not clear to me but this might be what you are looking for select l.license_num, cr.course_topic_code, cr.required_minutes from Course_Requirements cr join Licensees l on cr.license_level = l.license_level and cr.residential = isnull(l.residential,1);


0

This is Vertical Partioning, as Michael Green states. I think it really depends on how frequently you need the Description field for Books and Movies. If it's rarely accessed/requested, then split it out as you described above. You would have two additional tables though, like BooksMeta and MoviesMeta, instead of the one Meta table. Then add your keys to the ...


-1

For those purpose, I only use relations in the USER table with users ids stored as pointers inside. Using a cloud code function, I provide a parameter to add , del or whatever instructions to apply to a single or an array of users. Cloud gives the opportunity to create collateral events such as "a user like this" As a reading step, a query is applied to ...


0

Do not have GameScore or OvertimeScore. Instead have QuarterScore keyed by GameID and Quarter. For normal games there will be four rows per game. For overtime there will be 5, 6, or however many are needed. Total score and a count of quarters can be summarised as a column in Game, if this is useful.


0

If you created an OverTimeScore table, you'd then need to add an INT or BIT field to GameScore or Game table to check if overtime exists or not. Are you also going to store the total score? Or just each quarter score and calculate the total in your app/query? If this were me, I would add a total score field and an overtime field into my GameScore table, I ...


0

Possible solution to account for the helpful info in the above two answers: `form_control` form_id primary_event_id (foreign key in `event_dates`) default_event_id (usually same as primary date) primary_event_host_id (foreign key `event_speakers`) default_start_date_time (actual or arbitrary start point) default_end_date_time ...


1

No, you don't want to use a hash as the ID, especially if you are making the ID the Primary Key and will have tables Foreign Keyed to this table. An SHA1 hash is 20 bytes and cannot be decoded (i.e. you cannot derive the source values from it). It would be better (generally, the RDBMS being used hasn't been specified) for indexing if the values were ...


2

Generally not a good idea, for the following reasons: If someone knows UserID and times, they may be able to match that back to transactions (depending on how this ID is exposed - if it's exposed externally via an API, someone knowing UserID could generate candidate TransactionIDs from guessed times) High amount of entropy in the key means that your ...


2

Indexes with the columns in the order given: actions: INDEX(user_id_to, user_id_from) -- 'covers' first query actions: INDEX(user_id_to, counter) -- may subsume ORDER BY and LIMIT in 3nd query Either index will be useful for 2nd query. DROP KEY user_id_to (user_id_to) as redundant when you add my two suggestions. For various reasons, it may be better to ...


4

One row in a relational DB table holds one fact only. That user 12345 is connected to user 44444 and to user 55555 is two facts. Consequently it should be stored as two separate rows. Columns capture meaning from the real world. Depending on the table, there can be an implicit semantics between the columns. A good example is a departmental reporting ...


0

To solve this problem, I did the following: I created 5 tables: CREATE TABLE cust (customerID serial, customerName VARCHAR(30)); CREATE TABLE price (customerID INTEGER, itemID INTEGER, price INTEGER); CREATE TABLE item (itemID serial, itemName VARCHAR(5)); CREATE TABLE order_ (orderID serial, customerID INTEGER, orderDate DATE); CREATE TABLE orderItem (...


0

Got a good explanation from a user on a channel from freenode where I usually stay at. The person I argued with seems allergic to repeating values like, even if there's only 1 out of thousand of records he's willing to do his tables N-N. The user I talked to says, he seems to be a "classic DB people". Then he asked me this: "for your exact use case: ...


0

In theory, it is better you design a separate address table (without Person_fk in it), and then you can create an associate table, let's call it PersonAddress (Person_fk, Address_fk). This way, it does not matter where you have N:N relationship between Person and Address (i.e. a person may have multiple addresses or an address may host many persons).


0

The easiest and more realistic real world scenario to maintain and query is going to be either 1-1 or 1-N, choose whichever is appropriate for your business logic. Several of the products I've worked on have used either of these methods and they are far easier to maintain than trying to get N-1 to work. Maintaining N-1 for addresses would very challenging ...


0

I took a look at the REST API endpoint list on the Couchbase docs site. I think it might be hypothetically possible. You could use the N1QL REST API to do inserts, selects, updates, and deletes. I can't say for sure whether this is going to help scaling and load or not, but I do think you're going to have to address a number of other challenges: validation,...


0

Yes, it's correct, although, as noticed by yourself , it's pretty crude. This has been asked months ago. Why didn't anyone answer it yet? Are you still working on this project? I wonder if you really have the right skills to complete this... If it's indeed interesting, I could help you with the database architecture. One word of advice: MySQL does not ...


0

I'm not sure that I understood your question... That said, lemme try to help you. I undestand the model represents the categorization of 'parties' as begin part of 'segments'. These categorizations are mutable and have to be traceable in time. Let me humbly present what I think is a more complete LDM, where PARTY is explicited Segment Group Cd is ...


1

Avoiding NULLs is very low on my list of optimizations. I prefer to say "use NOT NULL wherever appropriate". That implies that if you need NULL, go ahead and use it. I do find in my own tables that I rarely have a use case for NULL. See Rick's RoTs for a longer list of recommendations; they are aimed at MySQL, and come from years of optimization ...


2

While I do use NULL columns, there is overhead. The Oracle documentation you retrieved this short list should explain if you read further. There are case where NULL indicates issues with data types and/or just having the column. Consider PHONE_NUMBER NUMBER(15): This will likely have a formatted phone number column, and may be null for numbers like 1-...


3

This is bad - it is a table scan LIKE %1234 But thousands of rows is not much Best would be to have second table with phone phone: userID PK FK to user number PK select * from user join phone on phone.userID = user.userid and phone.numer = '1234' if you don't want to fix the data design then two column with = will be ...


0

I think you've confused some syntax. Best way to do this is SELECT My_Fields[,,,,] FROM My_Table WHERE number LIKE '123%' OR number LIKE '456%'; This will work for VARCHARs, but not for INTEGERs. But, '%LIKE%' doesn't exist, unless you're trying to pick out the string 'LIKE' from a longer string. A search for the string - LIKE %123% makes no sense - it ...


4

I think that at the end, event_dates will need to be partitioned. That will allow you to archive old events. form_control may need this also, and probably primary_event_date_id could be a candidate. I think, however, that the key from the ongoing and events, should be located at the same way in the form_control. For doing that, you will need to convert the ...


0

Jeremy Cole in a series of posts explains InnoDB internals and on disk format in particular. I'd start from https://blog.jcole.us/2013/01/02/on-learning-innodb-a-journey-to-the-core/ . Get his tool innodb_ruby, it's very helpful in the learning.


2

TL;DR: Do not use an array. Use individual boolean columns. Your coworker may not be aware of actual storage requirements. Null storage is very cheap and efficient in Postgres. Do nullable columns occupy additional space in PostgreSQL? A couple of boolean columns are also very cheap and efficient - nullable or not. Actually, just 5 boolean columns ...


0

Create a form based on that table, and only include the fields you want them to be able to edit. Alternatively, you can add all the fields (for visibility) and disable (in the field Properties) the ones you want to be excluded from edits. They will still be visible on the form, but the data they display cannot be edited.


2

In your case I think you could consider the use of a Bit String Type data type. For instance, something like: CREATE TABLE yourtable ( booleans bit[5] default B'00000', ... other fields ... ) It is efficient in terms of memory and does not require the use of a complex type like a PostgreSQL array (actually it is a bit array), and more, you do not ...


0

The way you've outlined it is how I would do this in a data warehouse. I've created relationship tables (mapping tables) to tie different dimensions together, because there are potentially many to many relationships between them. And because my tables have thousands and millions of records each, creating a denormalized table would be incredibly cumbersome ...


1

This looks like a school project correct? I think the confusion is coming in because your example is very simple (perfectly appropriate for a class/training environment). The design you have is considered best practice. The reason it might seem simpler to go with the denormalized design is because you have so little data/so few columns. If you add a ...


1

Rapidly changing counters (view counts, LIKEs, etc) are best handled in a separate, 'parallel', table. It would have 2 columns: id of the object (the PRIMARY KEY) and the INT UNSIGNED counter (or other size of number). By keeping this 'small, rapidly changing' info separate from the 'bulky, seldom changing' rest of the data, a lot of contention is avoided....



Top 50 recent answers are included