Hi I'm designing a database to hold my domain objects. I have various validation rules on the objects such as string length and if it must be filled. These things are checked in the C# code. Does it make sense to duplicate this rules in the database, such as setting nvarchar(150) instead of nvarchar(max) and setting nullable to false f...
Usually the clustered index is created in SQL Server Management Studio by setting the primary key, however my recent question about PK <-> clustered index (http://stackoverflow.com/questions/2262998/meaning-of-primary-key-to-microsoft-sql-server-2008) has shown that it is not necessary to set PK and clustered index to be equal. So how s...
I have two select statements that I am trying to port from Oracle to Postgres: 1) (Note: this is a subselect part of a bigger select) SELECT 'Y' FROM CRAFT MA, CONFIG MAC, ARMS SM WHERE MCI.MS_INVENTORY_NUMBER = SM.MS_INVENTORY_NUMBER (+) AND MCI.AB_BASE_ID = MA.AB_BASE_ID_LAUNCH AND SM.ACT_AC_TYPE = MAC.ACT_AC_TYPE AND SM.MAC_ID = M...
I've been trying to figure out how to query a shopping cart database to find all Orders that contain tangible items (items can be downloadable, therefore, not shipped) that have not been assigned a UPS tracking label. I haven't been able to do it. The involved tables are as follows: // dbo.Inventory - details about the individual produ...
I have multiple user account types, each with different information. For example, business contacts link to businesses, school administrators and students link to schools. Students have physical addresses, but business contacts and school admins use the organizations address. There's other information unique to each type as well. I'm le...
You have a website with a user base. You want to allow some users to do more than others. How would you go about designing the DB to support that? Would it be a horrible idea to use a table like: users(id, name ... can_add_comments, can_edit_comments, can_add_items ...) ...
We have a large ERP style central system and we're looking at moving many small Access databases into it's rather inflexible structure. In most instance the data can be stored but the process is tortuous, breaks the existing data model and makes the system harder to use. Can anyone point me to good resources to explain the pros and cons ...
Hi All, I have an idea I have yet to implement, because I have some fear I may be barking up the wrong tree... mainly because Googling on the topic returns so few results. Basically I have some SQL queries that are slow, in large part because they have subqueries that are time-consuming. For example, they might do things like "give me...
I've got an ads table, with ads which can be promoted in two ways. They can be promoted in search results, so they come before the others, or they can be promoted to the front page, where a random selection of a few promoted ads are displayed. They are to be promoted for a certain time period. I'm not sure how to best do this. Another ...
i understand that a master/slave setup is redundant, in that data is mirrored to all slaves from a central master. how does this vary from a distributed architecture? ...
How can I find out if I have too many indexes on my MySQL table? For some of the tables that need to be accessed quite a lot, I've added about 2-4 indexes on the columns that will be accessed a lot. But how can I find out if they're doing more harm than good? ...
Hi, I have a PHP script with many restricted areas. In each of these areas I have a function that checks if the user have access to the current area by checking the "usergroup" table. That problem is I have over 100 columns now so I'm not sure if that's a correct database design. Please advise. Thanks. ...
Hey guys, I'm building a Volunteer Management System and I'm having some DB design issues: To explain the process: Volunteers can sign up for accounts. Volunteers report their hours to a project (each volunteer can have multiple projects). Volunteer supervisors are notified when a volunteers number of hours are close to some specified ...
I have different 3 types of users and each type of user can have columns and relationships with tables that another type doesn't, but all of them have login(Unique) and password, how would you do: create a table for each type or create one table for all of them or create a table for all of them only for login and password and separ...
I have written a simple forum in PHP using PostgreSQL. The forum consists of a number of subforums (or categories, if you like) that contain topics. I have a table that stores when was the last time a user visited a topic. It's something like this: user_id, topic_id, timestamp. I can easily determine what topics should be marked as unre...
My question may probably be an echo of a question previously asked here: http://stackoverflow.com/questions/452295/how-to-design-a-user-object-model-using-ms-roles-membership, but I'm asking again since there is no good answer yet and my question is geared toward the data model design anyway: So anyway, in my application, there are user...
There are products, sections and attributes. Each attribute can have up to 5 or 6 options. Example: Power 10 Volt 15 Volt 20 Volt And there are about 10 products in total, each product has up to 17 attributes applied to it. Example: Product 1 power - 10 volt color - red, yellow link - online, offline How would you setup the tabl...
I have a table feeds that contains a collection of code-description rows. e.g. Key | Value | Url fb | facebook | http://www.facebook.com tw | twitter | http://twitter.com ff | friendfeed | http://friendfeed.com A REST endpoint returns the contents of this table as a JSON array & it is displayed ...
Earlier today I asked this question which arose from A- My poor planning and B- My complete disregard for the practice of normalizing databases. I spent the last 8 hours reading about normalizing databases and the finer points of JOIN and worked my way through the SQLZoo.com tutorials. I am enlightened. I understand the purpose of data...
Hi im a little confused by this diagram http://www.b-eye-network.com/images/content/i030ht0104.gif (Last diagram in the article) 1 - In the "ACCOUNTING ENTRY" table it shows "DebitEntry" and "CreditEntry" i) is this two column or ii) is this two rows of data? or iii) is it two separate tables on its own, Acounting_entry_credit and Acc...