Tag Info

New answers tagged

0

This is not about natural and surrogate keys, but about concept of independent and dependent entities. Here is your original example And now a bit modified model where User is a dependent entity (note rounded corners). Here User can not exists outside of the context of the Client. I would say both are OK, but I prefer the second example. The ...


2

I think the conflict you mention stems from confusing data logic and business logic. The business rule may be that for whatever reason, you do not want to have the same product for multiple users of the same client. However, there is really no need to put this constraint into the database. If you don't want to allow this, put that check into your ...


0

In my experience the logical data model tends to be developed with multi-part, natural keys no matter how long they are. The physical schema will have surrogate keys as the primary key and unique constraints for the natural key. The surrogate keys are for practical reasons, as @David expaned on, not to follow any theory. Reference tables with terse, ...


2

This is opinion based, or at least quite subjective in the real world with the best solution varying depending on a number of factors the relative importance of each different people with disagree on, unfortunately. The question is whether you should ever create a surrogate key when there is an apparently perfectly suitable candidate key (compound or not) ...


0

I would examine your model and see if it needs some normalization. It would appear that the two types of model are sub-types of a common model. I have found this kind of implementation requires some design. Using the same id sequencing for both tables opens up a number of options for implementation. Creating an intermediate Model table containing the ...


0

Here are my answers: Define relationships based on your surrogate keys and not your business keys. This is because this is what the SQL engine will use to optimize queries and this is the reason you have surrogate keys. Since surrogate keys are often integers, this simplifies the joins from using different data types such as strings which are less ...


4

For very small tables, fragmentation is not only irrelevant, but nearly impossible to control. The first eight pages are allocated out of mixed extents, which are almost always going to be non-sequential. Only after an index has more than eight pages will it be allocated additional pages from uniform extents. At fewer than 1,000 rows, your clustered index ...


3

Here are the layers of abstraction that you need to go through to track who has (pays for) a GSM phone: Note that this is good for a point in time. If you need to track changes over time then everything becomes many-to-many with start/end dates in the intersection entities.


2

The core of the database must be the account, also the customers. They are giving the money for the company, there isn't any other possibility. Logically everything should reference some of an account. Second, the users authentifies themselves for the network of the company by their sim ids. Every sim card should have exactly one account (which will pay for ...


1

I'd suggest you have a think about this system's use cases. Work out in your own mind how the queries will look for the given design and for your alternative. Include scenarios where people move between slots. What do updates look like? Can business constraints be enforced in DRI? Can the desired values still be found? Having actual examples to talk ...


0

As Jon says, you would be better off storing bookings rather than availability. This will make any future changes to operating hours or booking conditions easier (slot length, minimum/maximum booking duration etc). Also, you won't have to pre-populate an availability table out to some arbitrary future date.


3

You need a compound unique index. Suppose your table is called photos. You can do this: CREATE TABLE photos_new LIKE photos; ALTER TABLE photos_new ADD UNIQUE INDEX pid_tid_index (pid,tid); INSERT IGNORE INTO photos_new SELECT * FROM photos; ALTER TABLE photos RENAME photos_old; ALTER TABLE photos_new RENAME photos; If it works out, then run DROP TABLE ...


2

If you have common attributes across non-exclusive tables, you should consider normalizing your tables. It appears that Client, Employee, Manager, and Developer are all sub-types of the Person entity. As such any attributes common to all types should be in the person entity, and attributes for the other entities should depend only on that sub-type. I ...


4

Much of this is a matter of taste and style. And more importantly: specific requirements and consistent conventions. However, there are good reasons for this generic advice: CREATE TABLE item ( item_id serial PRIMARY KEY, grp_id integer NOT NULL REFERENCES grp(grp_id) ); If you have an item_id, better make it unique and ideally a surrogate ...


1

How would I guarantee to the user that no matter how he types "chair" (singular, plural, with or without adjectives), he will get all the results that the database can provide? Wildcard queries could solve your immediate problem. For example, your products table may have the following entries: productID companyID productType ...


0

You are using the export graphics feature in Toad but you should instead use the print feature, but instead just printing it, print it to PDF. This will give you non-blurry ER-diagrams.


0

A common practice is to have a status column that indicates whether or not the representative is active. Deleting data that you may wish to analyze in the future isn't a good idea. Instead, you could do something like this: representativeID status statusDate ------------------------------------------- 1 inactive 2014-03-20 2 ...


1

bigserial is just shorthand for creating a sequence: CREATE TABLE tablename ( colname BIGSERIAL ); Equals CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename ( colname biginteger DEFAULT nextval('tablename_colname_seq') NOT NULL ); Also, if you these are your primary keys, then they are already indexed, so you don't need to index ...


1

MS Access Vs MySQL MS Access works only on Windows whereas MySQL works on almost all platforms. MS Access does not support Partitioning feature whereas MySQL has composite, Range partitioning support. MySQL is opensource so no cost involved. Performance in MySQL is way better than MS Access MS Access database is more suitable for desktop use with a small ...


1

As well as PostgreSQL (which I recommend), you could also look at Firebird (another excellent choice). What is your development environment? If you're replacing spreadsheets, another good option might be Oracle's APEX with Oracle XE. If you do decide to go down the MySQL route, try to avoid non-standard data types like SET and ENUM (porting becomes ...


0

Take a look at http://www.mysql.com/why-mysql/windows/excel/ MS Access has limitations, data types, storage size, users access control (since you're going to have it accessed by more than a user). i think using MySQL will be more useful as your database grows up. it is a powerful dbms and will open doors for future enhancements and migrations.


0

Your design seems like a good one for what you want to achieve. Keeping your fact table as simple as possible is a good practice i.e. keeping only foreign keys against likes/un-likes, which then links back to dimension tables, such as User, Content etc. Don't be shy to index your fact table, for quicker search and updates.


2

What you have seems pretty solid. How about create table vote ( user_id int not null, content_id int not null, time_recorded datetime not null, vote int not null, primary key (user_id, content_id), foreign key (user_id) references `user`(user_id) on delete cascade, foreign key (content_id) references content(content_id) on ...


2

First, if it ain't broke, don't fix it. If no-one is complaining about the speed of the system then leave it alone. Spend time and money on other things. Second, no, I don't think it is possible to over-normalise. That is, I think it is possible to spend more time on the normalisation process than your application might need, but unless you've spent tens ...


1

Did you try sphinxsearch (http://sphinxsearch.com/)? It is search engine, but not only. It can query very fast with group by, order, filters. Mysql should be good for fast writes with per-table and per-host sharding. You can split your sphinx index to several parts and use distributed search: http://sphinxsearch.com/docs/2.2.2/distributed.html ...


3

Redundancy does not mean: Columns that have the same data type Columns (or rows) that have the same data value (if this is coincidental) Columns that are foreign keys that link a child table to its parent (including the same data value as FK and PK in the respective tables) Redundancy is not about columns or rows for that matter. Redundancy is about ...


3

Separate fields. Every time. String operations are costly, especially cutting them up into bits.


0

You have pretty much 4 choices: NoSQL - definition Every record is stored as a set of Key/Value pairs. It is very flexible and fast. Not all of the report writers out there support this style of storage. There are many example database implementations of NoSQL. The one that seems to be most popular right now, is MongoDB. EAV - definition This is where you ...


0

Can you enumerate all the scenarios for which you would like to store this data? if there is a finite number of columns combinations that may be applied to the table, then try to model a "base table" with common columns that are gpoing to apply to all scenarios, then create more tables (to implement some kind of inheritance; this is known as subtype / ...


0

I beg to disagree with this author. The results are expected, but may not be the desired values. Null values don't work well with logical comparisons as the value is unknown, as is the result of a comparison. If you are using nullable columns as query criteria, you do need to handle them appropriately. These are some of method I have used: WHERE ...


0

Since any unit can be converted to another unit of the same type With the formula: y = ((x + xOffset) * multiplicand / denominator) + yOffset I would create a table which contains the unit types plus these 4 values. From Unit To Unit Unit Type From Offset Multiplicand Denominator To Offset 'milligrams' 'grams' 'mass' 0 ...


2

First you should clarify the relationships between your entities by asking these questions: Can a facilitator of a group be a user that is not a member of that group? Or a facilitator must always be a member of that group? Do groups always have a facilitator? (your design indicates: yes) Does every group have one facilitator only? (your design indicates: ...


1

An alternative might be to have a field in WorkGroupMember called IsGroupFacilitator (or something else suitably descriptive of its function) which defaults to whatever you decide should mean no (perhaps 0 if it is a numeric type) but can be set to a non-zero value (1 is considered the traditional non-zero value) for those users who are facilitators. This ...


0

Yes, it should be a Foreign Key. Creating of it will allow server to optimize the execution of queries joining Work Groups and treir Facilitators.


1

You could have a basic product table, then also a table to store different types of specs, then another table to store the specs themselves: products -------- id name (a few other necessary fields) product_spec_types ------------------ id name product_spec_values ------------------- id product_id (FK to product.id) spec_id (FK to ...


3

Improving on Remus' answer, you could use a more powerful compression, storing nothing in the database. How would that work? Just like the difference between a method and a generator in Python, the difference between a function that returns a value or yields it when needed. Lets call it "lazy evaluation". As an example, assuming the table will hold all ...


6

You have to use compression (columnstores should do it). I suggest RLE (Run Length Encoding), you can store (5HpHagT65TZzG1PH3CSu63k8DbpvD8s5ip4nEB3kEsreAbuatmU ,1MsHWS1BnwMc3tLE8G35UXsS58fKipzB7a ,1Q1pE5vPGEEMqRcVRMbtBK842Y6Pzo6nK9) as the value and 115792089237316195423570985008687907853269984665640564039457584007913129639936 as the run length. Document ...


4

Unfortunately there are only 1080 atoms in the visible universe. But with only one atom given, it could be possible to describe the data purely by its position in the universe. The database has 2266 records with 52 bytes each, that is a database size of 52⋅2266 bytes. That means there are 25652⋅2266 ≈ 101082 possibe states the database could adopt. Using ...


3

On an optimistic estimate, you have 100 atoms in the entire universe per record - or 2 per byte. Good luck...


10

Absolutely possible, because someone with the resources to store 6021188640340442162025691220451771208370039202613309330051794368412 Terabyte definitely has enough money to get a custom made database system for his purposes as well. I might be available as a contractor, for only 0.01€ per Terabyte.


4

If your column definition does not have default constraint and it is nullable, the nullable field will have null mark, when you add a row even when you specify DEFAULT keyword for your insert value. For example for the following table definition CREATE TABLE Test( ColA [nvarchar](50) NOT NULL, COLB [nvarchar](50) NULL) The first three insert ...


0

George, it is good to start with data modelling, but it takes some time to learn and understand the modelling practices, for your current requirement check this link Data Modeling Jump Start and Basic Data Model. There are lots of books available, get some materials and build your own data model.


0

Always look for standards that support your requirements. What is a standard that supports recurring calendar events? ICalendar RRULEs: http://en.wikipedia.org/wiki/ICalendar http://www.kanzaki.com/docs/ical/rrule.html You can either store the rule as plain text and parse it as needed, or use a database schema for them: SQL Schema: ...


0

you can use partitioning of the table. Partitioning refers to splitting what is logically one large table into smaller physical pieces. Partitioning can provide several benefits: Query performance can be improved dramatically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small ...


0

Make a different table with the a part for te message id table 1: id - message table 2: id - date See if you can somehow arrange the "table 2" by its date, and format the date as following: min - hour - dom - mon - wom - dow (dom = day of month, mon = month, wom = week of month, dow = day of week) You can use numbers as "12", or wildcards as "*", maybe ...


2

You need to normalize your schema. Your Employee_Owns_Asset table is trying to do too many things. Consider this schema: Note that your individual assets need to be normalized out into their own table. This lets you separate the two semantically independent pieces of information: What are the properties (brand, model, etc.) of each asset? Who (if ...


0

The first thing you should be doing is storing the message text out-of-line in a separate table. while that will incur the cost of a join to fetch it, it'll be a trivial b-tree index lookup, and will be massively outweighed by the benefits of having narrower rows to scan in the table containing scheduled events. I suspect that if you think of this like a ...


1

As Zoltan pointed out, unless you have MANY millions of rows, I don't see a scaling issue. There are also many libraries for scheduling things such as Quartz on Java for example. These will store the recurring schedule as a cron-like expression. Because your example above has a flaw, if the recurrence is every Monday, then it's 52 x number of years the ...


0

Your Order should be Primary Key which you can link to another table where order details will be given. Products { ProductID, Name, Price } Customers { CustomerID, FirstName, SecondName } Customer_Orders { OrderID (Primary Key) CustomerID ... } Order_details { OrderID ProductID Quantity }



Top 50 recent answers are included