I am designing this database that must maintain a history of employee salary and the movements within the organization. Basically, my design has 3 tables (I mean, there more tables but for this question I'll mention 3, so bear with me). Employee table (containing the most current salary, position data, etc), SalaryHistory table (salary, ...
Hi guys , we have created a simple asp.net/sqlserver 2000 based Business Process Management tool in which we have a feature that user can create simple one column form and attached to the process and define rules and path. We save the dynamically created forms controls values in EAV design tables but as the transactions is increasing we...
I don't have much experience designing DB. Though I have some rough theoretical knowledge. So, on to my problem. We have data on a bunch of excel files (yah, surprise!) and we want to move them to a DB. To simplify, let's say the system is a centralized alarm system. Data get collected from remote locations and displayed in a centraliz...
Hi, I'm going to build a book store in which we have 3 entities(classes): Seller,Buyer,Book. I've designed the database as the following details: - Both buyer and seller can buy/sell one or more books respectively. - A buyer needs a seller account if he/she wants to sell a book. - Buyers will offer their price and seller would like to se...
I have a table called 'movie2person' with 3 columns: movieID, personID and role. I use this table to connect betwen the 'movies' table and the 'persons' table... many-to-many relationship.. I have selected movieID and personID both as primary keys... The problem is that sometimes I need to enter the same personID for the same movieID se...
Hi I have a database table, let's call it Countries. Countries all have names, short names and continents. Let's assume that the longest name of any country in the world is 54 characters long. Should I set the maximum size to 54 or should I set it to e.g. 64 or something else? Will my choice affect queries or storage in any way? I ...
I work for a billing service that uses some complicated mainframe-based billing software for it's core services. We have all kinds of codes we set up that are used for tracking things: payment codes, provider codes, write-off codes, etc... Each type of code has a completely different set of data items that control what the code does an...
I am developing a web application in which I will have customers and suppliers. Initially I thought on using a Customers table and a Suppliers table. Then when I was thinking on bank transactions, I noticed that each transaction needs to refer to a customer or a supplier, so I thought on using a single table named Business in which I w...
Hypothetical example: I have Cars and Owners. Each Car belongs to one (and only one) Owner at a given time, but ownership may be transferred. Owners may, at any time, own zero or more cars. What I want is to store the historical relationships in a MySQL database such that, given an arbitrary time, I can look up the current assignment...
Is it possible to define primary and foreign keys for database Views in Microsoft SQL Server Management Studio? How? I'm trying to create an ADO.NET Entity Data Model to read from four old, poorly-formed database tables that I cannot modify. I've created Views of just the data I need. The four Views should map to a simple three-entit...
Hi folks, I'm creating a new DB and have a bunch of static data that won't change. If it does, it will be a manual process AND it will happen very rarely. This data is a mix of varchars and Geographies. I'm guessing it could be around 100K or so in total, over 4 or so tables. Questions Should I put these on a READ ONLY filegroup Ca...
Hi folks, all my tables have an Id field of some type (UserId, PostId, FooId, etc). I usually make this a Primary Key. A table I have is called Countries. It has CountryId SMALLINT Name VARCHAR(100) -- Yes, english country names only, in this column. AndSomeOtherFields. Now, I know the Name has to be unique. All country names are u...
How would you DBA's handle this? I have taken ownership of an existing app (VB6) and database that was written in 1999. The database design is fairly 'flat', meaning the main tables are fairly wide (100+ columns) and developers have continued to tack on additional columns to the end of the tables. This has resulted in columns that have...
When defining datatypes in a database, I have always had a problem with choosing whether to use integers or strings to store certain 'numerical' data. Say I am building Yet Another Address Book and there is a post code field. Provided that post codes are always a 4 digit number, which data type do I store it as? Integer or string? Techn...
I hear a lot about subtyping tables when designing a database, and I'm fully aware of the theory behind them. However, I have never actually seen table subtyping in action. How can you create subtypes of tables? I am using MS Access, and I'm looking for a way of doing it in SQL as well as through the GUI (Access 2003). Cheers! ...
I'm trying to sort out the database behind my company's website. We deal with both Sites and Residents. At the moment the Site Reference is included in the Residents table as a foreign key but, obviously, this is only in 1NF. Site references are of the form nnnnn (12345, for example). Each resident has their own reference within the Si...
When inserting records to a database table which has a 1:m relationship to another table, is it not best practise to ensure that the related table is updated (inserted) with a new record? Is there any consequence if just one of the related tables is updated? Also, if I update the table on the m side of the relationship (for example, a r...
Basically I want to create a comment system where comments may have parents that are also comments BUT I would also like them to potentially have parents that may be something else, such as users or products (ie, I want to be able to comment on products, users, other comments, or practically any resource) How should I do that? Current ...
We have a two tables with a one-to-many relationship. We would like to enforce a constraint that at least one child record exist for a given parent record. Is this possible? If not, would you change the schema a bit more complex to support such a constraint? If so how would you do it? Edit: I'm using SQL Server 2005 ...
Hello, I'm looking for some ideas about saving a snapshot of some (different) records at the time of an event, for example user getting a document from my application, so that this document can be regenerated later. What strategies do you reccomend? Should I use the same table as the table with current values or use a historical table? ...